update the destination table
I want to pull data from an source destination. How can I insert rows that are not already in the table and update rows that already exist ? ...
ssis-data-tranformations
SSIS - when a connection is down
Hi. I've got a simple SSIS package that I edit in VS2008. What is basically does is run the same SQL StoredProc, residing in different SQL servers, and aggregating their output into a flat file. Simply put, four OLE DB Data Source going to a 'Union All' to output. If one of the servers is down, how can I make the package skip the valid...
Custom SSIS Destination Adapter for Bulk Insert
Hi, I am trying to build a custom SSIS component which combines the functionality of Conditional split transformation and OLEDB Destination Fast Load. in theory, I want to pass a variable to my component and that variable will control my component whether it should run or not according to its value. I am reading http://media.techtarget....
Errorneous Row numbers in a SSIS task
Hi, I am importing a text file into SQL server table which has got number of constraints. I have created one package and associated tasks. At the end of a SSIS package execution, I want to know the erroenous row numbers which were not succefully exported to DB. Is any direct API or variable available in dts namespace to give this inform...
create new column dynamically
How will I create a new/virtual column after pulling data from source before putting into the destination ? which task should I use for creating a new/virtual column ?In my case, I want to create a column of datatype datetime and fill data on that field based on a condition. How is it possible ? ...
Pull data from an object
Using Script Task, I had stored data ie a Dataset or Datatable object to a SSIS variable of data type Object. I wanted to pull data from this SSIS object variable which contains a Dataset object of data, and store it to a destination. This is possible in Script Task itself. I know. But how is this possible by using other SSIS tasks ? We ...
Connection Manager in Script Component
How will we use Connection Manager in Script component, using OLEDB Provider ? I had tried using Connection Manager with OLEDB Provider and SQL, but failed. what is the correct way to use ? ...
Problem with SSIS 2005 flat file source - partial row which isn't actually a partial row
I'm currently working on an SSIS package to load mainframe logs from multiple server/file sources into a database. As it stands at the moment I'm using a foreach loop container to loop through a recordset containing filenames and load the files using a Data Flow task from a Flat File Source and File connection to an OLE DB Destination t...
Script Component Transformation
How do we use Script Component Transform? And how to call Connection Manager created in Script Component ? ...
SSIS MYSQL to SQL Datatype
I'm trying to copy data from MySQL to SQL Server 2008. My SSIS is generating error for time (DBTime) column in MySQL database. (cannot convert dbtime to dbtime2) What datatype can i use in SQL server for time? I tired nvarchar, varchar and also tried data conversion task but i get same error. ...
SSIS 2008 Lookup Transformation SQL Query loses formatting
Since the SQL query box of an SSIS Lookup transformation is almost unusable, I usually write my code in SSMS (formatted with Redgate's SQL Prompt tool) and copy it to the Lookup. When you close this window and later come back in, sometimes the formatting is preserved, but sometimes the carriage returns are replaced by the "square" chara...
How to avoid 'number stored as text' error when exporting data from SQL server to excel (SSIS)
This is what I am trying to do in the SSIS package: Copy blank excel sheet with headers as new file Run a data flow task populate data in the sheet. When a open the excel, all numeric data is shown with 'number stored as text' error. This stops all my charts from working. I would like to know how can I inform excel to treat the parti...
SSIS error information
I have 15-20 data task each in their own sequence container in a single SSIS. Is it possible to catch the data task or sequence which failed and email that details (task name or sequence name, cause for failure) to a user? ...
SSIS - Create a CSV file from 2 different OLE DB sources
I'm looking for a way in SSIS (2005) to create a CSV file from 2 or more different layout OLE DB sources. Again, the column layout is different for all the sources. EX. Source 1 A,1234,ABCD,1234 Source 2 A,ABCD,1234 Final CSV Result File A,1234,ABCD,1234 A,ABCD,1234 ...
SSIS: from XML file to multiple tables
So I just started diggin SSIS today, so don't hate too much if there is something obvious I am missing. So I have an XML file (from a third party) <root> <foo> <fooId>12345</fooId> <name>FOO</name> <bars> <bar>BAR 1</bar> <bar>BAR 2</bar> [...] </bars> </foo> ...
Convert string to DateTime in SSIS
The data from source has string data type and sometime has invalid dates, empty column ('') I want to check whether it is valid date and convert string to date time. How can i do this in SSIS? My string value is already formatted as mm/dd/yyyy. ...
Sequential and Parallel process order
What is the difference between Sequential and Paralle Process Order in Analysis Services Process Task, in SSIS ? ...
ssis sql 2000 image to 2008 varbinary Failed to retrieve long data for column
Hi, I have a task that to migrate the image type column from sql 2000 to varbinary type in sql 2008. The source column having 3812353 max datalength size for the column. The package always failed with following error message. [OLE DB Source [13177]] Error: Failed to retrieve long data for column "attch_file_content_t". [OLE DB Sourc...
Learning SSIS 2008
I would like to learn SSIS 2008. can any one guide me to get ebbok to learn SSIS 2008? ...
SSIS Post Execute phase takes too long
I have a SSIS Package (SQL Server 2005) which has a very simple control flow and a data flow. The package runs acceptably fast, but the Post Execute phase is ridiculously slow - the whole package takes 90 minutes to execute, from which 37 are the PostExecute phase. I don't have any event handler on that step, the source and the destinat...