Foreach loop with SQL Statement Source

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Foreach loop with SQL Statement Source

Author

Topic

dmccmd
Starting Member

2 Posts

Posted - 2009-11-23 : 12:34:33

Hello, I am very new to SSIS and have the following situation.

I have 13 sets of tables and in each set of tables are 90 days worth of data stored in its own table (so I have a table called GroupOne that holds the current data then GroupOne_11232009, GroupOne_11242009, etc all the way to 90 days prior, GroupTwo, GroupTwo_11232009, GroupTwo_11242009, etc. each table contains approx 13 million records).

We are migrating from multiple tables to a partitioned table in another database. I am writing an SSIS package to move the data in chunks (basically each daily table). I have a an Execute SQL Task that creates a select statement and stored it in a package level variable (i.e. Select * from GroupOne_11232009). I then have a ForEach Loop Container setup to loop thru each record in package level variable. I am trying to now take that sql statement and execute it use as the data source for a data flow task. I tried adding a data flow task but don't see how to configure the OLEDB source to execute the sql statement (SQL Command from variable doesn't show my variable nor a way to add one). Is there a better way to do something like this? Are there some samples that could help a new SSIS person?

Execute SQL Task:
General
Result Set: Full Result Set
Connection Type: OLE DB
Result SET
Result Name: 0
Variable Name: User::FactGroupOneObj (object type variable)

ForEach Loop Container:
Collection
Enumerator: Foreach ADO Enumerator
ADO Object Source Variable:User::FactGroupOneObj
Enumeration Mode: Rows in first table

Execute SQL Task In Foreach Loop Container
General:
Result Set: Full Result Set
SQL Source type: Direct Input
SQL Statement: Exec (?) (? should be the Select * from GroupOne_MMDDYYY)
Parameter Mapping:
Variable Name: User::FactGroupOneObj
Direction: Input
Data Type: Nvarchar
Parameter Name: @@SqlCommand
Parameter Siz: 10000
Result Set:
Result Name: 0
Variable Name: User::FactGroupOneObj_Rows

From here I don't know if I can use a Data Flow task because when I try and configure an Ole db source within the Data Flow Task to use a Data Access Mode of SQL Command from Variable, the variable isn't showing up (User::FactGroupOneObj_Rows) and I can't add one.

Thank you in advance for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 13:02:45

whats scope under which you created User::FactGroupOneObj_Rows variable?

dmccmd
Starting Member

2 Posts

Posted - 2009-11-23 : 13:14:37

The variable User::FactGroupOneObj_Rows is created under the For Each Scope. Since the I want the Execute SQL Task that runs the Sql statement to add its results to that variable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 13:32:37

then how will you get it in data flow task which is outside. give scope as package and then try

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources