FAQ Database Discussion Community


SSIS 2012 connection manager red arrow

ssis
I have project where there is allready connection manager created called ODS, and this connection manager used domain account before, now I have to switch it to user and password, the problem is that when I simple go to edit and change it doesn't work, but when I create new...

SSIS execute sql task get value from a query statement

ssis
I am using SSIS 2008 and put a simple query (not proc) in an execute sql task (control flow). The query generate one column with a single value, what I am trying to do is based on this value to decide whether to do the following tasks. I tried mapping...

Execute SQL task while importing Excel file to SQL server [closed]

sql,excel,ssis
I'm using SSIS to import data from an Excel spreadsheet into a SQL database. I have this working. I used a data flow task to Import from Excel then Move to OLE DB destination. However, I'd like to use a SQL query to add a column with the current date...

Can a SSIS 2012 package be deployed to SQL Server 2008 R2?

ssis,sql-server-2008-r2
I built a SSIS package in VS2013 and can't find any deployment instructions. One person mentioned he thought you can't deploy backwards. So before I rebuild the package in BIDS 2008, I want to make sure there is no way to deploy.

SSIS Foreach Loop failure

sql-server,foreach,ssis
I have created a lookup for a list of IDs and a subsequent Foreach loop to run an sql stmt for each ID. My variable for catching the list of IDs is called MissingRecordIDs and is of type Object. In the Foreach container I map each value to a variable...

How to run an SSIS package having excel source on a server where excel is not installed using SQL Server Agent Job

sql,excel,ssis
I have an SSIS package that reads data using a custom query and stores it into a target excel source. I am able to run the SSIS package successfully on a server where no excel is installed by doing a right click and selecting the Run Package option. When I...

Transform Flat File without delimiters

c#,regex,text,ssis
I would like to transform flat file test.txt to flat file test-output.txt. Below the scheme: EXAMPLE INPUT: test.txt COD/ID:37 PRJ/NAME: Josephy Murphy PRJ/EMAIL: [email protected] PRJ/DESCRIPTION: test37, test37, test37 ... COD/ID:38 PRJ/NAME: Paul Newman PRJ/EMAIL: [email protected] PRJ/DESCRIPTION: test38, test38, test38 ... . . EXAMPLE OUTPUT: test-output.txt (pipe delimited without labels) 37|Josephy...

string Does not contain definition for Where, C# in SSIS Package

c#,ssis
string Does not contain definition for Where, C# in SSIS Package, what is missing please? string ToASCII(string subjectString) { var newStringBuilder = new StringBuilder(); newStringBuilder.Append(subjectString.Normalize(NormalizationForm.FormKD) .Where(x => x < 128) .ToArray()); return newStringBuilder.ToString(); } ...

SSIS 2012 Transaction in ForEach Loop

ssis,transactions,ssis-2012,foreach-loop-container
I have a package that iterates several times, one for each Category. I put a transaction in the package such that each Category completes in full or not at all. If I have 3 Categories, A B and C, and Category B fails, I want to ensure that A and...

SSIS - execute N concurrent instances of the same task

sql-server,sql-server-2008,parameters,ssis,parallel-processing
There is an SSIS package I am currently working on. What it does is that it runs the very same SQL statement 10 times, each time with a different value of a parameter N (N goes from 0 to 9). Statements are independent of each other (they are simply INSERT...

ssis swap some values in a data flow if they match a lookup table

ssis,sql-server-2008-r2,transform,lookup
Here's my problem - Midstream in my data flow, we have some values in one column that we want to swap for other values based on a lookup table. For example, if I had a rowset like this: Key Value 1 A 2 B 3 A 4 C 5 D...

SSIS: How to check if string value from one column of a table matches with string value (from the right) in a column of another table?

ssis,string-comparison
I am pretty new to SSIS and below is the SQL query which explains what I am trying to do: select * from Table1 t1 join (Select ID, LEN(ID) as len from Table2) as t2 on RIGHT(RTRIM(t1.col) , t2.len) = t2.ID There might be some better query in SQL for...

How to import a package from SSMS to Visual Studio for local machine?

visual-studio-2010,visual-studio,ssis,ssms
I'm trying to mess around with an SSIS package on my local machine. Currently, this package is in SSMS under a server. When I try to export this package, save it on my desktop and doule-click to open it, a deployment wizard comes up. What I want, is the package...

Incremental Appending Value in SSIS

ssis
Is there a way in SSIS to go through a certain column in a table and to collect incrementally the values for each row in this column and to finally insert the final value to a second field or parameter? Is it possible to do it with no external code?...

SSIS WMI query to count errors in applicaiton event log

sql-server-2008,ssis
I know there is a WMI Data Reader task, but I dont want ALL the error log results, just a count. Also you cant seem to do COUNT(*) in a WMI query, you have to count your results afterwards? So, what is a good way to count these results. I...

How to map variable to column

ssis
I have one Data Flow Task, in DB Source I have a Query and in DB Destination I insert in one table with this Query, but I have one more column, I need set to this column a variable value. Anybody can help me?...

Aggregate Transformation vs Sort (remove Duplicate) in SSIS

ssis,data-warehouse,business-intelligence,bids
I'm trying to populate dimension tables on a regular basis and I've thought of two ways of getting distinct values for my dimension: Using an Aggregate transformation and then using the "Group by" operation. Using a Sort transformation while removing duplicates. I'm not sure which one is better (more efficient),...

SSIS 2008 - Get Current Date in Variables

sql-server,ssis
In SSIS 2012 I can get current date using GETDATE() function with expression in variables. Though, in SSIS 2008 expression fields does not exist. Question: How can I get current date dynamically with variables in SSIS 2008?...

SSIS package execution succeeds but doesn't do its job

sql-server,ssis,package,sql-server-agent
I use SQL Server Agent to fill tables in DataWarehouse. I have about 50 steps in the job and every step is run with a proxy account. And every step works correctly besides one: SSIS package which contains about 20 Execute SQL Tasks which execute procedure. This is what I...

How do I resolve a 0x80040e14 and 0xC0202071 in SSIS?

sql,visual-studio-2013,ssis,sql-server-2014
I am extremely new to SSIS and may need allot of things explained. What I am trying or attempting to accomplish is this: Connect to an XML site using SSIS in VS-2013 Delete the existing data in the table Dump the parsed XML data to the table I am getting...

Does Amazon RDS for SQL Server support SSIS?

sql-server,ssis,amazon-rds
Reading some conflicting answers from Google searches, not sure if the answer is Yes, No, or maybe.

How do I add a column to the Lookup No Match Output in the SSIS Lookup Transformation?

ssis
I'm trying to clear up some of the clutter of an SSIS package I have by removing (hopefully) unnecessary transformations. I have a Lookup Transformation that matches incoming addresses by County Name and adds a column with the FIPS value of the County when matched. I would like to add...

Error by schedule ssis package in sql server 2008 agent

sql-server,sql-server-2008,ssis
When I execute my SSIS Package manually it works just fine, But when I try to execute it from SQL agent i always get an error. In my case the SSIS is called from a certain path in the server. . The error is as follows: Also when I See...

Script Task in SSIS Input0Buffer Error

ssis,vb.net-2010
I need to change existing SSIS package to add new columns. This package has a script task that compares OLD and NEW values. It's giving me bellow errors. at ScriptComponent_2dc377607ff1474a82d85cf977045c1c.vbproj.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) at ScriptComponent_2dc377607ff1474a82d85cf977045c1c.vbproj.UserComponent.Input0_ProcessInput(Input0Buffer Buffer) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) Here is the VB code: #Region "Private Constants" Private Const...

Access denied SSIS w/ Parameters via xp_cmdshell

ssis,sql-server-2008-r2,xp-cmdshell
I have searched up and down for a solution and cannot find anything that helps. Everything that I have tried doesnt seem to work. I have two database. I cannot modify Database A by adding a table or anything like that. I cannot enable cross-database access. I believe I cant...

SQL Agnet,SSIS packet and Error: DB2 OLEDB not registered perhaps no 64 bit registered SSIS Packet

sql-server,ssis,agent
I canot execute my SSIS packet from SQL Agent! I always get this error "DB2 OLEDB not registered perhaps no 64 bit registered SSIS Packet" I tried everything like deactivate run time 64 to false, also changing the check box in the sql agent to allow the package to run...

SSIS error - value violated the schema's constraint for the column

ssis,excel-2007
Exporting from sql server to excel with ssis. export gets around 80% through writing to the excel file then fails with this error. It stops at the same row each time. The value in the field being written is the well within the length parameter and contains no funky characters....

Assign Dimension value to SSIS variable

sql-server-2008,ssis,ssas,ssas-2008,data-cube
I have data cube hierarchy as follows. I can access the highlighted node as SELECT [Calendar].[Report Days].[All Members].[All].[WantInReport].[Yesterday].LastChild ON 0 I tried to run this query in Execute SQL task and assign the output to an SSIS variable. But issue is the column name is changing. I tried to alias...

RESTful API call in SSIS package

vb.net,ssis
As part of a SSIS (2005) package I am calling a RESTful API through a script component (VB.NET). I am getting the records back OK. The problem I have though is processing the format it comes back in so I can output it (split into the separate output columns) within...

SSIS, Foreach Loop, Event Handler when it writes 0 records due to file containing zero records

ssis,event-handling
I have a foreach loop container in my SSIS package. the Data Flow task imports xls files from a certain directory. If the xls file contains no data, the package shows in the log that it wrote 0 records. This is fine; however, I would like to envoke an event...

Transform and copy table data

sql,ssis,sql-server-2008-r2
Sorry if this questioned was asked before, but i am posting it, since was not able to find anything related in forum. So any help would be appreciated. Here is my scenario. We have an SQL table view A (just read access) with set of data located on Server A...

Parent-Child parameters in SSIS 2012

sql,sql-server,ssis
Is it possible to create Parent-child parameters in SSIS 2012 like in SSIS 2008?

Changing variable scope in SSIS programatically

c#,ssis
Is there a way to change a variable scope in SSIS programatically? for example, this variable will be created on the package level: package.Variables.Add("sSourceObjectId", false, "User", 1); But I need to create the variable on the OnError scope, because the system variable ErrorCode is not visible to the package level....

Using SSIS tools?

ssis
I want to import data from a relational database in order to integrate it and load it in a transit database that I will use to form OLAP cubes. I've seen many tutorials about SSIS and they're all so basic and working on just one data flow task. Now I...

pivot not working in SSIS

sql,sql-server,ssis
My SQL query works fine when i execute it in SQL management studio but throw the error below when execute in SSIS package. Any advise? **When click on Build Query of the syntax - it shows The PIVOT SQL construct or statement is not supported. Case 1: SELECT listid, [1]...

SSIS Package Component Intermittently Failing

sql-server,ssis
I have an SSIS package that seems to be having concurrency issues. I have set it up in a way that each of the 6 containers can be run all at the same time, or individually. They all run a SQL stored procedure and store data into a table object....

SSIS “Failed to lock variable” error in File System Task within foreach Loop

variables,foreach,ssis,source,ssis-2012
In SSIS 2012, I have a Foreach Loop and tasks within it to import data from a CSV file and then move the file to an archive folder. In the Foreach Loop Editor, there is a single Variable listed in Variable mappings: User::FilePath. The File System Task within the Foreach...

Converting to SSIS 2012 Project Deployment Model

sql-server,visual-studio-2012,ssis,ssis-2012
I'm converting our 2008 SSIS packages to 2012 and I want to use the project deployment model. I opened the SSIS packages in Visual Studio 2012 and had no issues converting them to 2012. I chose the simplest package and right-clicked and picked Convert to Package Deployment Model. I went...

How to Parse text file using SSIS Script Component with Complex and Varied Data

c#,sql-server,regex,ssis
I have a text file that holds data based on a series of tags. Tag A represents a column with CRLF delimiter, Tag B is a separate column with a semi-colon delimiter, and Tag C contains values delimited by under scores and CRLF delimiters. I've created a C# Script component...

SSIS Lookup multiple identical databases

ssis,components,integration,lookup,multiple-databases
I'm working on a project where i need to do lookups on a data warehouse server in Integration Services. My problem is that I need to be able to change what database it i performs the lookup to. The databases are design wise identical. I have solved this problem with...

ssis - No value given for one or more required parameters

sql-server,ssis
I am working with SSIS 2008. i am getting error on sql task editor query as Error: Executing the query "SELECT SUBSTRING(?, 8, 2) + SUBSTRING(?, 10, 2) +..." failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query,...

SSIS ScriptTask hangs on Debug

ssis
New to SSIS packages. I am working with an SSIS Package in Visual Studio 2013 after installing SQL Server Data Tools for Visual Studio 2013 https://www.microsoft.com/en-us/download/details.aspx?id=42313 When I edit the ScriptTask, it opens Visual Studio 2012. I place a breakpoint on the line of code I want to hit in...

excel datasource returning nulls with sql command

excel,excel-vba,ssis,integration
I'm reading excel sheet that has a column formated as number but every now and then contains letters. These are returned as NULLS. I need the values. I added IMEX=1 and that did not help. Changing Values in the registry is not an option. the columns are fixed so is...

With SSIS, the flat file connection manager messes up with csv files having “,” in values in the range of thousands

csv,ssis,connection-manager
I had data in a CSV file, which appears in a notepad something like this: In SSIS, when I try to load this file in a Delimited format, the data which appears in the preview gets messed up due to the commas which occur in the numeric values, eg. in...

How does not | (Bitwise OR) work in SQL Server/SSIS 2012?

sql-server,ssis,bitwise-operators,bitwise-or
I've read about the Bitwise OR and it seems to be like it functions same as OR except it's faster. I read https://msdn.microsoft.com/en-us/library/ms186714(v=sql.110).aspx And here is the example they give: USE tempdb; GO SELECT a_int_value | b_int_value FROM tableName How is this expected to run? It makes no sense, you...

SSIS - How to modify Event Handler

visual-studio-2013,ssis
Using VS2013, Built an SSIS package that works. Added a custom error logging task to fire on error in the event handler tab. I executed the package and got an error in the event handler execution so I opened the evnet handler tab so I could fix it, and the...

SSIS Execute Package Task variable path

ssis
In SSIS 2008 I have 2 packages (package1 and package2) . I need call the package1 from package2, but the path changes on other servers . How I can set the path using variables?

SSIS Execute SQL Error: “ ”

sql,sql-server,ssis,sql-server-2012,ssis-2012
I am getting an error when trying to Execute an SQL file with an Execute SQL Task in SSIS (2012). Execute SQL Task: Executing the query "" failed with the following error: "". I am trying to automate a process to import a file from TestRail that drops and recreates...

Visual Studio 2013 opens sln in text editor

visual-studio-2013,tfs,ssis
Recently, my copy of Visual Studio started opening solution files in a text editor rather than loading the solution when I attempt to open solutions from the Source Control Explorer. If I use File -> Open -> Project/Solution or navigate to it in Explorer it opens normally. I am running...

Convert or output SSIS package/job to SQL script?

sql-server,linq,entity-framework,linq-to-sql,ssis
I understand this may be a little far-fetched, but is there a way to take an existing SSIS package and get an output of the job it's doing as T-SQL? I mean, that's basically what it is right? Transfering data from one database to another can be done with T-SQL...

Sorting files with the same header names using SSIS

ssis,ssis-2012
I have a folder with a lot of data files in. I want to be able to loop through the files, look at the headers and sort them into folders if they have the same headers. Is that possible to do in SSIS? If so would anyone be able point...

Load data for yesterday's date and specific date

ssis,ssis-2012
I have a ssis package, which runs on date parameter. If we dont specify the date it always load data for yesterday's date. And if we give any specific date like '2015-05-10', It should load for that date. How can we achieve this dynamically (using package configuration)? Once we load...

Check Excel File Sheet for missing columns

vb.net,excel,ssis,excel-2010,vb.net-2010
I have an ssis package which takes an excel file and imports it, but if any of the columns in the sheet are missing then the import fails. Im trying to write some code to check the column header in a sheet to make sure it contains a set list...

SSIS Flat File Source Text Qualifier being ignored

sql-server,ssis
I am using SSIS to insert data from flat file to database. I have created Data Flow Task for that. I am using Flat File as Source and ADO NET Destination to insert data. Below is how my setting looks like for Flat File Source. Below is how my "Columns"...

SQL Server 2008 SSIS SQL Provider not found

sql-server,sql-server-2008,ssis
I am migrating an SSIS package from a 32bit server to 64bit server, both running SQL Server 2008. The problem is the SQL Server connection string - when I use SQLOLEDB as the provider, it says 'data source name not found and no default driver specified'. I've checked the ODBC...

validation error when project parameter sensitive property is set to true in SSIS 2012

visual-studio-2012,ssis,sql-server-2012,catalog
I am using SSIS 2012 and deploying projects via project deployment model. I have 3 project connection managers and passing the password information to the connection manager through a project parameter. When I set the Sensitive property of password parameter to False the package runs fine but when I set...

SSIS - What is difference between Error and Truncation?

ssis
What is the difference between "Error" and "Truncation" in configuring error page of SSIS component? Especially, What type of different errors can come under "Error" that are different from truncation? ...

Can I prompt user for Field Mappings in SSIS package?

sql-server,ssis,etl
I am trying to build a tool to facilitate some redundant importing of data into a SQL Server database. The flat text files we get have are mostly static, but there is often about a 5-10% variance in field names and sometimes some extra fields added (in which we will...

for each loop container nested SSIS

ssis
Can you create a foreach loop container within a foreach loop container? As the handling of variables for this situation? Object type have two variables, which take the value of two fields come from a database and query SQL Server 2012 on an Oracle, to return me two camps. The...

How to scale concurrent ETL tasks up to an arbitrary number in SSIS?

concurrency,ssis,scale,etl
Problem (See Context below) How can I scale individual tasks (e.g. downloading and parsing) to an arbitrary number of concurrent executions (e.g. 500) in SSIS? Setup Description Our setup is that we have a list of feed urls we want to visit, get all items and insert them into the...

SSIS package data flow tasks report

reporting-services,ssis,etl
We have many SSIS packages that move, import, export around large amount of data. What is the best way to get alerts or notifications if expected amount of data is not processed? or How to get daily report on how different SSIS packages are functioning. Is there a way to...

How to use SSIS API to read a package and determine task sequence from code (PrecedenceConstraints)

sql-server-2008,ssis
I am writing aprogram which uses the Microsoft.SqlServer.Dts.Runtime library to load a package from XML. Currently, I am extracting all of the connections and tasks but I am not currently capturing the precedence sequence. I used the following short debug snippet to dance around the objects and properties that looked...

Uploading Excel Report Data to Sql Server

sql,sql-server,ssis,ssis-2012
I want to upload my Custom Excel Reports to SQL Server Database. I designed the tables for reports. uploading the data for 1st month works fine but when I upload the data for second month it will replace the other data in the table. I don't want to replace the...

Project consistency check failed. has a different ProtectionLevel than the project

ssis,sql-server-2012
I have this annoying problem where it says that the project and package have different protectionlevels. Which is not true, they are all the same set to "EncryptSensitiveWithUserKey". I tried this Protection level changed mid project - now project won't build and some other solutions, non worked. Whenever I want...

Control flow task in SSIS stops after first one

sql,ssis
I've looked around yet can't seem to find a solution for my problem. I have a control flow in SSIS to import files. (Looks like new members can't post images so you can find the link here. The pattern repeats for several files.) The script task checks if the file...

How to incorporate a text qualifier in a file name used as a variable?

c#,csv,ssis
I have a C# script which takes in two CSV files as input, combines the two files, performs numerous calculations on them, and writes the result in a new CSV file. These two input CSV file names are declared as variables and are used in the C# script by accessing...

How to remove a row in SSIS when a value is not a date

ssis
I have a massive excel spreadsheet I'm trying to port over to a database via SSIS. There are some rows that don't have a date value and I don't them moved over (they are group headers and total lines). I'm using a conditional split to handle those, but I don't...

Archive file in Script task using c# in ssis

ssis
I want to archive file with date using Script task.filename will be like abc_x1.csv. file will be coming monthly once next file comes like abc_x2.csv Need to archive the file with whatever name after _.csv with date before the extension. iam using the below code this is not working. can...

How to do File System Task in SSIS depending on Result of Data Flow

ssis
I'm writing a (what I thought to be a) simple SSIS package to import data from a CSV file into a SQL table. On the Control Flow task I have a Data Flow Task. In that Data Flow Task I have a Flat File Source "step", followed by a Data...

Execute Data Flow Task based on condition in SSIS

sql-server,ssis
I am very new to SSIS and I am building a package which will import files. I have a Execute SQL Task which will execute a SQL statement which will return files that needs to be imported, but the files will need to go into different tables depending on which...

Sql code to get multiple columns out of single column

sql,sql-server,ssis,multiple-columns
I have a column like "AP.1.12345.ABCD.20150523_0523.20150524_0223". Can i divide the column into multiple columns on basis of " . " ? Say for above example column, the output will be like AP 1 12345 ABCD 20150523_0523 20150524_0223. Thus single column will be converted into 6 columns. ...

Create Derived Column in SSIS that resets on Change of ID

sql-server,ssis,derived
What I'm hoping to do is to create a derived column that creates an incremental value, but resets on a patient ID. From the screenshot below, what I currently have are the columns DCACCT, DCCODE, and DCDESC. But I want to add the derived column that contains that increments by...

How to specify a specific Windows Event Application log for logging from SSIS package

ssis
I'm new to SSIS packages but want to set up process logging to the Windows Event Log. Is there a way to specify an event log other than the general windows application log?

SQL server force multitheading when executing SSIS package

sql-server,multithreading,sql-server-2008,ssis
Here is the question: I am using VisualCron to run a ssis package on SQL Server 2008 R2. The SSIS package will run a query which get millions of rows and output it into a flat file. Sometimes, I found when I run this SSIS package, the sql server doesn't...

SSIS error connecting to Excel Source

reporting-services,ssis,sql-server-2012-datatools
I have a 64 bit machine. One of my projects I have the properties of the project for Run64BitRunTime = False and I still get the following error when attaching to an Excel xlsx source in order to import into SQL 2012. Error 1 Validation error. Data Flow Task: Package1:...

Is it possible to recover the SSIS packages that are deployed in SQL Server?

sql,sql-server,ssis,ssis-2012
I have developed few SSIS packages in my local system and deployed it in to remote SQL Server. Now my local hard disk crashed. Is there a way to get back the packages that are deployed to SQL Server? Thanks in advance...

Data streams in case of Merge

sql-server,ssis,sql-server-2012,sql-azure,sql-server-2014
We are seeing enormous amounts of data-traffic to and fro our SSIS server. We cannot find the culprit. Is there any way to find out which package is causing all the trafffic? Any advice on that? We are thinking that maybe all the merges we do cause all the traffic....

WinSCP .NET assembly throwing “The winscp.exe executable was not found at location of the assembly” when installed to GAC for SSIS

ssis,gac,winscp,winscp-net
I have a package which downloads files from FTP using WinSCP which runs perfectly when I execute it locally. But we are facing issues when we run from the deployed version from SSIS DB Catalog. GAC registration already done for the .dll file. Below is the error that we are...

Conditionally Loop Through and Execute Stored Procedures in SQL Table

sql-server,ssis
I currently have a SQL Server table consisting of a number of stored procedures, each stored procedure belongs to a parent chapter. For example: Stored procedures with IDs 1001-1199 belong to Chapter 1 Stored procedures with IDs 1200-1299 belong to Chapter 2 Stored procedures with IDs 1300-1399 belong to Chapter...

Why does FireError fail in C# 2012, but works in VB, while FireInformation works in both?

c#,vb.net,visual-studio,ssis,sql-server-2012
I have an SSIS package in Visual Studio 2014, and I want to raise an error in a Script Component if any records traverse a particular path out of a 3rd party transformation. I WANT to do this in C# 2012, but the FireError method gives an error: The best...

Updating Rows In a Table That Was Used to Import From SSIS

sql-server,ssis,package
I am currenly creating a SSIS package that selects the top 2000 entries from one table and insert those entries into another table. How can I update those entries in the original table (my select contains a condition whereby I check where the status is still empty) so in my...

Using an Integration services project within a Windows Form or WPF app?

c#,sql,excel,ssis
I am trying to make an application to display and add up some data in excel sheets but using a SQL server. The gist of the app is that i can use a windows form or WPF to choose a directory path and select an excel file. Then the app...

Using SSIS, how to insert new data from Excel sheet into my existing SQL Server table

sql-server,excel,ssis
I have imported some data from Excel file into my SQL Server table using SSIS. As because it's a report of profit & loss statement it has a header named as Trading Name, address fiscal year etc. In SSIS, I've used OLE DB Source and used the query like Select...

Convert SSIS (Visual Studio 2013) to work on SSIS 2012

visual-studio-2013,ssis,sql-server-2012
Is there a way of converting a SSIS package created in Visual Studio 2013 to work on SSIS 2012? The start of my package looks like this: <?xml version="1.0"?> <DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:refId="Package" DTS:CreationDate="12/18/2014 8:53:33 AM" DTS:CreationName="Microsoft.Package" DTS:CreatorComputerName="LOCAL111" DTS:CreatorName="username1" DTS:DTSID="{C6D60123-5529-4BC1-B426-B219A0709EB7}" DTS:ExecutableType="Microsoft.Package" DTS:LastModifiedProductVersion="12.0.2430.0"...

Data Flow Task with variable source

c#,excel,ssis
In an SSIS package I need to put the content of 2 SQL queries in 2 sheets of an Excel file. The results of the 2 queries are in 2 object variables, I'm wondering if I can use a Data Flow Task for this. Issues : I don't know what...

Replacing long Hypen in SSIS code (Find and Replace)

sql,ssis
Would like to ask a way to find and replace a long Hypen in my column product title, Sample Productname:Frozen – Meat xxxx xxxxx xxxx Would like to remove the long hypen before passing it to our final database Result should look like this: INSERT INTO [Product].[Market].[Products] ([ProductId] ,[ProductSourceCd] ,[ProductTitle])...

SSIS take oracle variable and use them in a ssis resulset

sql-server,oracle,ssis,resultset
Hi I have an execute sql task in one of my ssis package. I need to check if some of my tables have values inside them and store the result in a variable.I then need to use those variables later on in my package. My question is how do I...

Automated file import with SSIS package

sql-server,ssis
I am very new to SSIS and its capabilities. I am busy building a new project that will upload files to a database. The problem I am facing is that files and tables differentiate from one another. So what was done is I created a table that will map each...

SSIS Dataflow CSV to SQL Server with DateTime2 column

sql-server,ssis
I'm trying to load a flat file into SQL Server via SSIS Data Flow task. As far as the file, I'm getting the column in this shape 20140311115000, If I turn Fast Parse: False I can get the column to import if I change the column to 2014-03-11 11:50:00. This...

Import custom text format without separators [closed]

c#,sql-server,regex,ssis,etl
I would like import this .txt file format to SQL Server Table or to convert each block of text to pipe separated line. Which tools or C# solution suggests you to resolve this issue? Any suggestions would be appreciated. Thank You. ================= INPUT (.txt file) ================= ID: 37 Name: Josephy...

SQL Server or SSIS code for incrementing

sql-server,ssis,increment
Input ID 12345 12346 12346 12387 12568 12387 12387 Output ID - Value 12345 - 1 12346 - 2 12346 - 2 12387 - 3 12568 - 4 12387 - 5 12387 - 5 I have an input like shown above and I need an output also shown. So looking...

Distance between two zip codes using Google Maps API and a set of pairs of zip codes in Excel [closed]

sql-server,excel,api,google-maps,ssis
I have a set of zip code pairs in Excel. My aim is to use the Google Maps API or any other good source and get the driving distance between these zip codes for each pair. Is this possible? If these data points were in an SQL Server table, is...

BIDS 2012 SQL Log provider: The connection xxx is not found

sql-server,visual-studio-2012,ssis,bids
I'm facing a weird situation while configuring logging for packages. I created a new project from scratch, configured SQL Server login provider with a package OLE DB connection and everything runs fine. If I promote the aforementioned connection to become a project connection, then I get this error all the...

Reading XML using XmlDocument

c#,xml,ssis,xml-parsing,xmldocument
This is my feed. <feed xml:lang=""> <title>NEWS.com.au | Top Stories</title> <link rel="self" href="http://feeds.news.com.au/public/atom/1.0/news_top_stories_48_48.xml"/> <link rel="alternate" href="http://news.com.au"/> <id>http://news.com.au</id> <rights/> <entry> <title>F1’s glaring issues exposed</title> <link href="www.google.com"/> <author> <name>STEVE LARKIN</name> </author> <link rel="enclosure" type="image/jpeg"...

how to convert Oracle geometry to SQL GEOMETRY

sql-server,oracle,ssis,geometry
Ihave some geometry datatype entries in one of my table in a Oracle database.I would need to import those data into a sql server table to then use them for something else. I am aware that I cannot do that with an ssis package.There might also be some conversion problems.Could...

SSIS The parameterized sql statement yields metadata which does not match the main SQL statement

sql-server,ssis
I'm getting the above error when trying to execute a custom lookup. What I'm trying to do is lookup the minimum promotional price between date ranges. The SQL executes fine in SSMS SELECT refTable.PRICE FROM ( SELECT MIN(PRICE) AS PRICE , NAME , ITEMRELATION , FROMDATE , TODATE FROM [dbo].[AllCustPrices]...

How to use Oracle connection in SSIS?

oracle,ssis,oledb,oracle12c
I must develop a SSIS Package with Oracle connection. I'm using Windows 8.1 64bit with Visual Studio 2010 and Oracle version 12c(remotely). I would use "Microsoft OLE DB Provider for Oracle" like picture: Where I insert my connection parameters: After "TEST CONNECTION" command I get this error: [UPDATE] After installation...

visual studio version for XP users support ssis

sql-server,visual-studio,ssis
which is the latest visual studio version for Windows XP that supports SQL Server Integration Services so I could run ssis projects? Thanks

Exporting to CSV file in SSIS from query is mixing up rows and columns

ssis
I am trying to export data from a query to CSV in SSIS. But the resulting CSV file is mixing up rows and columns. I am using |as a delimiting character.The problem comes when the column data is few i think. Could it be a datatype size issue? Update: Here...

Accessing parent parameters from child package SSIS 2012

visual-studio-2012,ssis
I know this subject has been covered here several times but I could not get any of the answers worked for my problem. I have 7 packages running in a Master Package and 7th child has 3 parameters. To be able to let user define those 3 parameters, I have...