I have a table with the Source TableName and Source ColumnName. I am trying to use SSIS to extract data from my source based on the tables and columns. I am using a Script Task to dynamically create the SELECT statement, no problems with that. Next, I have a data flow task with a Source OLE DB Connection and the Data Access mode is using this dynamically created select statement. I am however unable to use this because this SELECT statement variable is not populated until runtime. Basically I want to COPY ALL ROWS from the source to a destination. Is this possible? What is wrong with my assumptions? Any suggestions? Thanks!You are so wise...like a miniature budha covered in fur. -Anchorman

Hi, I suppose the table that has the Source Tablename and the Source Columnname has only these 2 columns. Say this table's name is "Metadata". You can have an Execute SQL Task in the beginning of your package that would retrieve the row count from this table. Save this row count in a user variable, say @[User::RowCount]. Create another user variable @[User::Counter] to use it in the For Loop container. Have a For Loop container. The InitExpression would be @Counter = 1; EvalExpression would be @Counter <= @[User::RowCount]; AssignExpression would be @Counter = @Counter + 1 In your For Loop container, have an Execute SQL Task. Here, query your Metadata table. The query would be - SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY SourceTableName) As ID , SourceTableName , SourceColumnName FROM Metadata ) TempInner WHERE ID = ? Pass the @Counter variable as the parameter to this query. Save the SourceTableName and SourceColumnName in user variables @[User::SourceTable] and @[User::SourceColumn] respectively. Create another user variable @[User::Query] and set its "Evaluate As Expression" property to TRUE. Set its expression as - "SELECT " + @[User::SourceColumn] + " FROM " + @[User::SourceTable] As soon as the values of the variables @[User::SourceTable] and @[User::SourceColumn] changes, this expression would be evaluated which would generate a sql query and save it in the @[User::Query] variable. Have a DFT after this Execute SQL Task in the For Loop container and use @[User::Query] variable as the source of the data in your source component. The above solution would loop through all the records in the Metadata table, generate queries and move the data from source to destination. Please let us know in case the above solution doesn't work for you or you need any clarifications. Cheers, ShalinShalin P. Kapadia

you need a loop structure. let me clarify steps to you: first of all , you need a data flow task with a source pointing to the table which contains source tablenames and column names, then put script component task and create sql statement based on the source table name and column names comes from the source ( you can use the code you wrote in the script task as you had before here ) now use RecordSet Destination and write results in an OBJECT type variable. then go to control flow tab add a foreach loop container after first data flow task, set enumerator as ado enumerator , set object variable there. you need another variable of string type in package scope, let's name this one as SQLQuery in the foreach loop container editor, in the variable mappings tab, set variable name with User::SQLQuery and index with 0 then put another data flow task inside foreach loop container, set the second data flow task with OLEDB Source and set sql query from variable as access mode, and set User::SQLQuery as source variable there, then set destination as you want. That's all.http://www.rad.pasfu.com

Actually, what you are stated above is not my question. I already have a FOR EACH LOOP that creates the select statement. The issue I have is copying the columns from Source to Destination. Assume, User:SelectStmt has the select statement for the source (this is what gets populated in the foreach loop) now inside my DFT, I have an OLE DB Source that is using SQL COMMAND VARIABLE for the DATA ACCESS MODE. Now, how do I populate the destination table? I tried a copy column, but with that you have to manually map columns. Since my SQL is dynamic, that didnt work. For purposes of this question, assume that I already have my select statement. My questions is, how do I copy columns from source to destination given I have the source select statement already dynamically populated at runtime. Thanks You are so wise...like a miniature budha covered in fur. -Anchorman

if you want to have DYNAMIC Destination table, just you need to use OLEDB Destination with access mode as "table name or view name variable" and then set a string variable name pointing to destination table. do you mean anything else?http://www.rad.pasfu.com

Right. So, in the query that you are generating dynamically, please change the expression to - "SELECT " + @[User::SourceColumn] + " AS SourceColumn FROM " + @[User::SourceTable] This would mean that whatever is the source column name, you are setting its alias as SourceColumn. What you need to do now is to map this SourceColumn to your destination table's column. While developing the package for the first time, you can just hardcode a query and complete developing your DFT. Once this is done, you can change the Data Access Mode of your source component to variable and set this variable as the source of query. This should work for you. In case it throws any validation errors, you can set the DelayValidation property of your DFT to TRUE. Please let us know if you're still facing issues. Cheers, ShalinShalin P. Kapadia

So this is what I have so far inside the data flow task OLE DB SOURCE | | COPY COLUMNS | | OLE DB DESTINATION So I have the OLE DB Source as SQL COMMAND VARIABLE, I just realised that you have to have some data in that if not in spits an error. And the OLE DB Destination has TABLE NAME VARIABLE. Two issues: 1) Since the select statement is dynamic, how is the mapping within the COPY COLUMNS task? 2) In the OLE DB DESTINATION, although its using a table name by variable, its still asking to MAP the columns before hand. Mapping this doesnt really make it useable. If it helps: the source column names and the destination column names are identical. Thanks You are so wise...like a miniature budha covered in fur. -Anchorman

You don't need a copy column transformation ideally, i suppose. A copy column would just create a copy of the column in the pipeline. This doesn't copy the source column's data to the destination column. If its just a direct dump of data from source to destination (i.e. if there are no transformation on the source data), you don't need any transformation between your source and destination components. You just need to map the source column directly to the destination column. It could cause issues at design time, so as mentioned in my previous post, while developing the DFT for the first time, just hardcode a query so that you get the source column name. Map this column to the destination column. Once you are happy with your DFT, change the source's data access mode to variable and set the variable. Also, as mentioned in my previous post, you need to give an alias to your source column. So, please change the expression to - "SELECT " + @[User::SourceColumn] + " AS SourceColumn FROM " + @[User::SourceTable] This means that your source column name would always be SourceColumn. This would ensure that the DFT doesn't fail since the metadata of the DFT remains the same/fixed. A question for you - your destination table and column remain fixed or are they also dynamic? Thanks, ShalinShalin P. Kapadia

My select statement has several columns, its not just 1 column. So my dynamically created User:SelectStmt would have something like so: SELECT column1, column2, column3 FROM table1 I tried mapping directly from the OLE DB SOURCE to the OLE DB DESTINATION without the COPY COLUMNS task, but on the destination, it still asks me to MAP columns. "A question for you - your destination table and column remain fixed or are they also dynamic?" Everything is dynamic. This data is stored in a table with columns SOURCETABLE, SOURECOLUMN. So my dynamic select statement that I generate gets every column for a single tablename and then creates the select statement as above.You are so wise...like a miniature budha covered in fur. -Anchorman

...and as mentioned in one of your previous replies, the destination columns and table names are identical to the source columns and table names. One more question for you - are the number of columns fixed or do they change? So, in your example above, you've mentioned a query that has 3 columns. Assuming that all the tables would have 3 columns, your dynamic query would be as follows - "SELECT " + @[User::SourceColumn1] + " AS Column1, " + @[User::SourceColumn2] + " AS Column2, " + @[User::SourceColumn3] + " AS Column3 FROM " + @[User::SourceTable] In your destination component, set the data access mode to "Table Name or View Name Variable" and assign the same variable that has the dynamic query. Once this is done, you would have to map the Column1 from the source component to Column1 of the destination component. Likewise Column2 and Column3. Cheers, ShalinShalin P. Kapadia

yes, you need to map columns when you use data flow task. what is your source and destination types? if both are sql server on same server, you can use this structure for data import: Insert into DestinationTable(col1,col2,...) select col1,col2,... from SourceTable note that in this way you just need to use execute sql task, no need to data flow task.http://www.rad.pasfu.com

Shalin, data flow solution will not work with DYNAMIC columns mappings. source and destination columns may differ each time, and this needs column mappings change each time, and data flow task will not do it in runtime.http://www.rad.pasfu.com

Shalin, The number of columns change based on the different tables, so no, it is not always three, it could be 3 it could be 30. I dont see the point of aliasing the column names given that the column name in the source and destination are identical. Given this scenario, I cant map the columns because the columns change for the different tables. Reza, The source is actually Oracle and the destination is SQL Server. I just need a basic extract of all the oracle data into the sql server db for data cleansing and transform. Thank you both for your help so far, but it doesnt seem like I have a solution yet.You are so wise...like a miniature budha covered in fur. -Anchorman

Shalin, data flow solution will not work with DYNAMIC columns mappings. source and destination columns may differ each time, and this needs column mappings change each time, and data flow task will not do it in runtime. http://www.rad.pasfu.com Reza, I think this summarizes my issues. What is your suggestion given that this is my issue? ThanksYou are so wise...like a miniature budha covered in fur. -Anchorman

Reza Mohamed, The reason why I was aliasing the source columns was to align with the metadata of the DFT. It has nothing to do with the source and destination columns being identical. The DFT fails if its metadata changes. So, this is the reason why I was aliasing the source column names. Irrespective of the actual source column names, the DFT would always treat it as the alias given to it which would always remain the same since we are setting it explicitly. Now that you've informed us that the number of columns aren't fixed, it is difficult for you to implement this using a DFT because, as mentioned above, if the metadata of the DFT changes (number of source-destination columns, their names, datatypes, etc), it fails. You would have to consider other options - one of them was suggested by Reza Raad above of using an Execute SQL Task; however, because your source is Oracle and destination is SQL Server, I don't think you would be able to use this approach unless you have setup a linked server to the Oracle database. Perhaps consider splitting the DFT based on the number of columns - 1 DFT for all the source and destination with 2 columns, 1 for 3 columns, 1 for 4 columns. But this doesn't seem to be the right approach either. One thing you could do is to setup a linked server to your Oracle source and have a SQL stored procedure that would generate the insert queries dynamically and run them. You can call this stored procedure from your SSIS package. Reza Raad, I didn't know that the variable name data access mode doesn't work in the destination component (surprisingly the SQL query works. I would have to do some more reading on this perhaps); i was trying it in my machine now and it threw error. Thanks for this information. Cheers, ShalinShalin P. Kapadia

Reza, The source is actually Oracle and the destination is SQL Server. I just need a basic extract of all the oracle data into the sql server db for data cleansing and transform. I think you can not automate it in this way, you can not use data flow task, because it needs to define column mappings. you can not use Insert into ... select ... because it need to have source and destination on same sql server. if you just want to import all tables data from oracle to sql server , you can use Import/Export Wizard, this wizard after configuring the source and destination , let you to choose which tables you want to transfer from oracle source to sql server destination. this is a work around not a solution, and if you want to do data transfer based on the table which contains source tables and columns, you can not use this way too.http://www.rad.pasfu.com

My initial attempt at this problem was to use SQLBULKCOPY within a C# script task. Everything works perfectly, I am able to dynamically extract data from the Oracle source and dump into the SQL destination. Within the script task, i am using an OLEDBDATAREADER to get the data from Oracle and then SQLBULKCOPY to write to the SQL destination. BUT, my issue with this was that I had no way of redirecting the error rows that may happen. It was an all or nothing extract, but my situation requires me to redirect the error rows into a table where all the columns are VARCHAR, and then I can do whatever I want with those error rows later. Do you know of a way to redirect error rows within a SQLBULKCOPY? thanks You are so wise...like a miniature budha covered in fur. -Anchorman

I think you can not import tows ROW BY ROW with sqlBulkCopy, because as you said this is batch import ( all or nothing ) if you are scripting this, I suggest to use SQLCommand with ExecuteNonQuery for each row in each table, you can catch the error rows and import them in a BAD-ROWS table. note that performance of this way is not like SQLBulkCopy, because import will be done row by row and slower of course. but this is a possible way.http://www.rad.pasfu.com

Can you give a quick example of SQLCommand, I think i tried this but I was facing the same issue. Are you saying to catch the error rows in a TRY-CATCH block? I think thats what I tried, but I wasnt sure how to get it to move along.You are so wise...like a miniature budha covered in fur. -Anchorman

this is an example of sqlcommand(note that you need sqlconnection to use in the sql command too , in the sample sql connection named myConnection assumed to configured before) : SqlCommand myCommand = new SqlCommand(myExecuteQuery, myConnection); myCommand.Connection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); yes, you should catch errors with try-catch block, note that your sql command will only insert One Row each time, and if it cause error, only that row will not import not others.http://www.rad.pasfu.com

Reza, Im familiar with the SqlCommand object, my question was particularly towards an example of how to redirect the error rows. So from your example above, if I add a USING statement and/or a TRY-CATCH block: using(SqlCommand myCommand = new SqlCommand(myExecuteQuery, myConnection); try{ myCommand.Connection.Open(); myCommand.ExecuteNonQuery();} catch { <--- is it here that I catch the error row? if so, how? how do i insert into error table? does this still continue along with the extraction of the rest of the rows from source? or do I need to do something to make it continue? } finally { myConnection.Close(); } I hope my dilemma is a little clearer now. You are so wise...like a miniature budha covered in fur. -Anchorman

OK, you should use another sqlcommand in the catch block, which inserts bad rows to another log table, for example: catch { myCommand.CommandText="insert into ...."; myCommand.ExecuteNonQuery(); } first, Try block , tries to import record in the destination table, if it fails the catch block will import it in a log table. Note that you should use for loop to do this step for each row in each table.http://www.rad.pasfu.com

Got it. But just to clarify, with the first try catch block, even if it catches something does it still insert the rest of the records in my source? That is the purpose of this entire problem. Thanks You are so wise...like a miniature budha covered in fur. -Anchorman

Got it. But just to clarify, with the first try catch block, even if it catches something does it still insert the rest of the records in my source? That is the purpose of this entire problem. Thanks yes, it does, BUT, you should do import ROW BY ROW,if you do this, valid rows will import to destination table, and failed rows will import to Error or Log table. http://www.rad.pasfu.com

What do you mean by a row-by-row? How would I do this? EDIT: are you saying have one sqlcommand for the select and another for the insert? while the datareader is reading, then do the insert? Thanks

I mean that: you should read data from source tables within the script you can read whole data in a data table . then use foreach loop to loop through data rows in data table. then build sql command with each row columns, and then use above code to insert that row in destination table all steps should be in script. does it make sense to you now?http://www.rad.pasfu.com