| Author |
Topic |
|
sqlmyworld
Starting Member
38 Posts |
 Posted - 2011-09-18 : 20:25:02
|
| I am new to SSIS world. I have parent pkg which sets connection string and gets record set (e.g. select * from table) to call child pkgs. same child pkg should be called until all record set have been finished. child pkg should get 4-5 parameters like file name, table name etc and load data using bulk load.issue - when I called child pkg by foreach loop container. only one same pkg executed every time which I hv set up at child pkg level for initial set up. No run time argument executed even though I have set up child-parent variable using pkg configuration. what is the best solution to call same pkg using run variables (or record set)? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-18 : 23:55:49
|
| you need to store the result of your first query in a recorset variable created in parent package. Then use for each loop to iterate through recordset and extract required information from it and store it in individual variables created in parent package (filename,tablename etc). Inside For each loop add execute package task to call child package and pass the parameter values thorugh variables. Inside child package create configurations for required properties and take values from parent package variable by using config type as parent package variable------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sqlmyworld
Starting Member
38 Posts |
Posted - 2011-09-19 : 03:01:19
|
| Visakh, I have already done what you have suggested but when I run parent package it couldn’t pass captured variable value to child package. May be I miss something somewhere which I couldnt find out now.For info, I extracted five columns (like table name, data file name etc) into five diff variables of parent pkg using record set. Also, I created one System object (objTabName) which is used as ADO object source variable in “ForEach loop container”. Enumerator --> “Foreach ADO Enumerator” and Enuneration Mode --> “Rows in first Table” are used. All variable including system object has parent package scope.In child pkg, I have truncate table, drop indexes, bulk insert tasks. I also created 5 variables (table name, data file names, success number, truncate number, failure number) with Child pkg scope and set up parent package configuration to get Parent variable values. Below are examples of variable configuration between parent and child pkgConfig Name Config Type Config String Target Object Target PropertyConfig 1 parent pkg Variable table_name Child_Table_Name ValueConfig 2 parent pkg Variable Data_file Data_file Value I think it couldn’t pass data file name and destination table name from parent pkg to Bulk insert task of child pkg. For example, If I select a recordset with two diff values (eg. table 1 and table 2), the parent pkg called child pkg twice but it loads for the same static table (eg table 3) which I use to set up connection for Bulk Insert Task. Once both pkg run successfully, I can see correct success & truncate number have been logged in log table which belongs to table 1 and table 2. This numbers are parent variable numbers which are passed to child pkg but only parent destination file and table name are not used during bulk loading process. Did I miss anything or is there any way I can dynamically set up Source file name and Destination table name?Thanks in advance |
|
|
|
sqlmyworld
Starting Member
38 Posts |
Posted - 2011-09-19 : 03:04:02
|
| In parent Pkg, I also mapped all 5 variables in "Foreach loop editor window" using index 0, 1, 2, 3 and 4 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-19 : 04:33:24
|
| where are you mapping the table name property of bulk insert task to variable value passed from parent?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sqlmyworld
Starting Member
38 Posts |
Posted - 2011-09-19 : 19:38:24
|
| I think that’s the issue. I don’t know where to map bulk insert task variables passed from parent other than parent-child pkg configuration. Where should I map table name and data file name for bulk insert task?For info, I mapped other Execute SQL tasks (like truncate table, drop indexes etc) using parameter mapping within Execute SQL Task Editor but haven’t mapped bulk insert task other than static connection of flat file (tab 3). |
|
|
|
sqlmyworld
Starting Member
38 Posts |
Posted - 2011-09-19 : 22:07:34
|
| I also tried to set File connection manager dynamically using Expression (map data file variable) but it didn’t work. Am I follow right approch? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-20 : 03:07:51
|
quote:
Originally posted by sqlmyworld
I think that’s the issue. I don’t know where to map bulk insert task variables passed from parent other than parent-child pkg configuration. Where should I map table name and data file name for bulk insert task?For info, I mapped other Execute SQL tasks (like truncate table, drop indexes etc) using parameter mapping within Execute SQL Task Editor but haven’t mapped bulk insert task other than static connection of flat file (tab 3).
you should be doing it from expression tab of task. you've expression builder where you can give an expression and map it to task property------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sqlmyworld
Starting Member
38 Posts |
Posted - 2011-09-21 : 03:04:14
|
| Visakh,I had already created two expressions in bulk insert task but there was one mistake I have done. I created two expressions, one for “DestinationTableName” (for table name variable) and another for “SourceConnection” (for data file variable). That’s why I got error messages and couldn’t process further. Then, I removed expression “SourceConnection” from Bulk Insert Task and created an expression for data file in “File Connection Manager” and map this expression with “Connectionstring” property.Bulk insert taskProperty ExpressionDestinationTableName @[User::Child_Table_Name]File connection Manager:Property ExpressionConnectionString @[User::Child_Data_File]So by this way, I can get variable values for Destination table in Bulk Insert Task and its relevant Data File in File Connection Manager and I successfully loaded all tables and data file using foreach loop container.I really appreciate your help for fixing this issue. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-21 : 05:49:29
|
| no problemyou're welcomeglad that i could help you out------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
prob - Call child pkg by Foreach loop Container