For Loops
-
Can I use for loops to iterate through db connections? I have a series of odbc connections that I want to cycle through and run the same queries on each db and do things with the data. I'm trying not to create 47 separate jobs.
Wasn't sure if you could pass an odbc connection string as a param to a for loop.
-
Matthew Cushing (1/19/2009)
Can I use for loops to iterate through db connections? I have a series of odbc connections that I want to cycle through and run the same queries on each db and do things with the data. I'm trying not to create 47 separate jobs.Wasn't sure if you could pass an odbc connection string as a param to a for loop.
Yes, you can. You have to store your connection string in package variable. And then setup your connection string property in the connection manager to be setup by expression using the a fore mentioned variable. Give it a try and let us know how it goes.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
CozyRoc (1/20/2009)
Wasn't sure if you could pass an odbc connection string as a param to a for loop.
Yes, you can. You have to store your connection string in package variable. And then setup your connection string property in the connection manager to be setup by expression using the a fore mentioned variable. Give it a try and let us know how it goes.
Okay, I've set up a variable called ODBCName with a scope of testDeploy (the package). I'm unsure of the next step. What I am looking to do is to have one package that grabs information from 47 databases. Does that mean I need 47 variables to pass as params? I'm totally flying in the dark here.
I'm assuming I'll need a for loop to run thru each of the database odbc connections.
-
Matthew Cushing (3/16/2009)
CozyRoc (1/20/2009)
Wasn't sure if you could pass an odbc connection string as a param to a for loop.
Yes, you can. You have to store your connection string in package variable. And then setup your connection string property in the connection manager to be setup by expression using the a fore mentioned variable. Give it a try and let us know how it goes.
Okay, I've set up a variable called ODBCName with a scope of testDeploy (the package). I'm unsure of the next step. What I am looking to do is to have one package that grabs information from 47 databases. Does that mean I need 47 variables to pass as params? I'm totally flying in the dark here.
I'm assuming I'll need a for loop to run thru each of the database odbc connections.
You need only one variable and you use this variable in the expression. You have to have a list with databases and each database iteration will be stored in the variable.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (3/16/2009)
You need only one variable and you use this variable in the expression. You have to have a list with databases and each database iteration will be stored in the variable.
I guess that's where I'm getting lost. I'm not sure how to set up the expression. Right now, I have a control flow with several data flows on it. Each dataflow calls a common ado.net connection so it can hit the progress database I'm pulling the data from.
From what I am understanding, the package can remain the same, I just need to change the connection manager to pull the next connection string into it. Where can i put the list, and how should I have the values configured? something like this?
Dsn=building1;host=abc.def.com;port=4502;db=DBNAME;uid=userIDThatCanLogIn
Dsn=building2;host=abc.def.com;port=4503;db=DBNAME;uid=userIDThatCanLogIn
Dsn=building3;host=abc.def.com;port=4504;db=DBNAME;uid=userIDThatCanLogIn
Dsn=building4;host=abc.def.com;port=4505;db=DBNAME;uid=userIDThatCanLogIn
I've never written any scripts or functions for SSIS. I'm still working from the book I got in the class I took a few weeks ago. do I put all of the connection strings into a text file and load that as the File Data Source in a new connection manager?
I appreciate the patience you've shown me so far 🙂
-
Matthew Cushing (3/16/2009)
CozyRoc (3/16/2009)
You need only one variable and you use this variable in the expression. You have to have a list with databases and each database iteration will be stored in the variable.
I guess that's where I'm getting lost. I'm not sure how to set up the expression. Right now, I have a control flow with several data flows on it. Each dataflow calls a common ado.net connection so it can hit the progress database I'm pulling the data from.
From what I am understanding, the package can remain the same, I just need to change the connection manager to pull the next connection string into it. Where can i put the list, and how should I have the values configured? something like this?
Dsn=building1;host=abc.def.com;port=4502;db=DBNAME;uid=userIDThatCanLogIn
Dsn=building2;host=abc.def.com;port=4503;db=DBNAME;uid=userIDThatCanLogIn
Dsn=building3;host=abc.def.com;port=4504;db=DBNAME;uid=userIDThatCanLogIn
Dsn=building4;host=abc.def.com;port=4505;db=DBNAME;uid=userIDThatCanLogIn
I've never written any scripts or functions for SSIS. I'm still working from the book I got in the class I took a few weeks ago. do I put all of the connection strings into a text file and load that as the File Data Source in a new connection manager?
I appreciate the patience you've shown me so far 🙂
You can put your connection information anywhere you want. The idea is that it should be a list you can iterate over. I would advise a SQL table. If you use SQL table, you have to:
1. Use Execute SQL Task to pull the connection information list. Set it to include the complete resultset and store it in package variable (type object). Depending on the connection manager you have used, the result will be ADO Recordset (ADO Connection Manager), DataSet (ADODB.NET Connection Manager), etc.
2. Setup foreach container with the result variable from step 1.
3. Setup iteration variable (you have to find out what does the variable will in fact contain. Include Script Task in the for each container and debug to find out what is in it).
4. Somehow you should come up with the connection information value from the iteration variable. I leave this to you to find out 😉 Store this value in another variable. This variable is the one you should use in your connection manager expression.
5. You can setup expression for connection manager by right-clicking on it and selecting properties. Scroll-down and find Expressions. Setup your connection string to point to the variable from step 4.
6. Insert your dataflow in the for each container. This data flow will be execute for each connection information you need to.
---
I'm not saying this task is trivial, but you will gain very good understanding of how the SSIS platform works.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (3/16/2009)
You can put your connection information anywhere you want. The idea is that it should be a list you can iterate over. I would advise a SQL table. If you use SQL table, you have to:1. Use Execute SQL Task to pull the connection information list. Set it to include the complete resultset and store it in package variable (type object). Depending on the connection manager you have used, the result will be ADO Recordset (ADO Connection Manager), DataSet (ADODB.NET Connection Manager), etc.
---
I'm not saying this task is trivial, but you will gain very good understanding of how the SSIS platform works.
This is really what I was hoping for. I'm the development/db/business analysis departments all rolled up into one and I'm trying to get things set up properly from the beginning. Gaining a good understanding of SSIS is crucial. And not having you hand me the answer is helpful.
Okay, so I have set up a new project. I dragged an Execute SQL Task into the Control Flow and set the following values:
ResultSet: Full result set
Connection Type: ADO.NET
SQLSourceType: Variable
Connection: ??
SourceVariable??
I'm assuming that the source Variable is something I set up before dragging this. So if I create a variable called ODBC Connection, with a scope of Package, a DataType of String and a value of 0, I can use this where it says SourceVariable?
I'm unsure what to set up for the Connection Manager if I'm going to make it variable. I have to use ADO.NET and pick ODBC because it's a Progress DB and nothing else works. Are you saying that the information I pull from the db for the first row will have fields that will drop into variables I've set up in the connection string in the connection manager?
2. Setup foreach container with the result variable from step 1.
I don't drop the sql task into the ForEach loop container, do I? I'm wondering if this foreach will contain the sql task to get the connection plus the dataflow tasks to do the work, or if the data flows sit outside the foreach.
I'll stop there because I'd like to understand this part first.
Thanks!
M@
-
Matthew Cushing (3/16/2009)
CozyRoc (3/16/2009)
You can put your connection information anywhere you want. The idea is that it should be a list you can iterate over. I would advise a SQL table. If you use SQL table, you have to:1. Use Execute SQL Task to pull the connection information list. Set it to include the complete resultset and store it in package variable (type object). Depending on the connection manager you have used, the result will be ADO Recordset (ADO Connection Manager), DataSet (ADODB.NET Connection Manager), etc.
---
I'm not saying this task is trivial, but you will gain very good understanding of how the SSIS platform works.
This is really what I was hoping for. I'm the development/db/business analysis departments all rolled up into one and I'm trying to get things set up properly from the beginning. Gaining a good understanding of SSIS is crucial. And not having you hand me the answer is helpful.
Okay, so I have set up a new project. I dragged an Execute SQL Task into the Control Flow and set the following values:
ResultSet: Full result set
Connection Type: ADO.NET
SQLSourceType: Variable
Connection: ??
SourceVariable??
1. Connection contains the ADO.NET used to connect to your server.
2. SourceVariable is the variable holding your SQL Statement. Usually you are using this when you want your SQL Statement to be dynamic. In your case I don't think it is needed. I would use the DirectInput type.
I'm assuming that the source Variable is something I set up before dragging this. So if I create a variable called ODBC Connection, with a scope of Package, a DataType of String and a value of 0, I can use this where it says SourceVariable?
I'm unsure what to set up for the Connection Manager if I'm going to make it variable. I have to use ADO.NET and pick ODBC because it's a Progress DB and nothing else works. Are you saying that the information I pull from the db for the first row will have fields that will drop into variables I've set up in the connection string in the connection manager?
2. Setup foreach container with the result variable from step 1.
I don't drop the sql task into the ForEach loop container, do I? I'm wondering if this foreach will contain the sql task to get the connection plus the dataflow tasks to do the work, or if the data flows sit outside the foreach.
I'll stop there because I'd like to understand this part first.
Thanks!
M@
The connection you use in the Execute SQL Task is for pulling the list of connection strings. This list will be used in another connection, using the approach I have described before.
Matthew my help is already bordering on me building the solution. I would prefer first for you to try and spend a couple of hours trying to find out how the platform works and then once you are comfortable with it, I can help you with the small details.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Sorry about that. I felt stopped by not being able to set up the connection right.
Thanks for your help.
[update 11am]
I think I understand now. I need to set up a sql task to read values I have put into a table on my sql server for each of the connections I want to make.
So if I have an execute sql task set up to read from a table: dsn, host, port, db, uid, and pwd, I can then use those values to feed the string into a connection manager set to point at a progress db.
It took me reading it 10 times to see what I was missing.
-
Matthew Cushing (3/17/2009)
Sorry about that. I felt stopped by not being able to set up the connection right.Thanks for your help.
[update 11am]
I think I understand now. I need to set up a sql task to read values I have put into a table on my sql server for each of the connections I want to make.
So if I have an execute sql task set up to read from a table: dsn, host, port, db, uid, and pwd, I can then use those values to feed the string into a connection manager set to point at a progress db.
It took me reading it 10 times to see what I was missing.
Hey Matt,
Good job. You are progressing 🙂 Keep us posted how it is going.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
I think I figured it out.
I have a table on my sql machine that has a table with two columns, one for the connection string, one for a building ID. (i'll need it later)
I have an execute sql task set to pull into a full result set.
I have a for each loop set on the ADO object to iterate thru, dropping the first column into a variable I have set up
Inside the loop, I have a data job that has a data reader that I grab that variable and drop it in.
There's a really good SSIS example by Brian Knight on Jumpstart TV - http://www.jumpstarttv.com/Player.aspx?vid=38&plid=&searchid=
it was really helpful.
The one thing I did find an issue with was setting the connection in the datareader to the variable. I just set up an expression to pull it, but it kept telling me I hadn't initialized it. Turns out you have to initialize the variable you use, so I dropped in the first connection string and it worked.
Since this is going to be a common set up for a lot of the stuff we're going to be doing in the future, I'm thinking of using this small set up - sql task, for loop and somehow calling other packages inside the for loop - using this as a template. Gonna play with it more tomorrow, but figured I'd share the break through.:-D
-
I am having an issue that you might be able to help me with - I've got it all set, but I would like to have it so that if a connection did fail for some reason, that it make a note of that and keep going. Is there a way to set the forloop to continue on error and let me know somehow that the 2nd dataflow object failed for this particular connection?
I'm running it for 47 buildings and there will be possible connection problems, or row issues that will disrupt it. My favorite is the one I'm having now:
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
-
Matthew Cushing (3/20/2009)
I am having an issue that you might be able to help me with - I've got it all set, but I would like to have it so that if a connection did fail for some reason, that it make a note of that and keep going. Is there a way to set the forloop to continue on error and let me know somehow that the 2nd dataflow object failed for this particular connection?I'm running it for 47 buildings and there will be possible connection problems, or row issues that will disrupt it. My favorite is the one I'm having now:
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
What you can do is setup an "Execute SQL Task" before your data flow, doing some idle operation like SELECT * FROM WHERE 1=0 and then if successful continue to data flow. If not, direct the failure to log the error and continue execution.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
The issue is in the datareader, so going out to the progress db is an issue. When using For Loops, is there a certain number of connections I can make or something like that causing an issue?
CozyRoc (3/20/2009)
If not, direct the failure to log the error and continue execution.
I guess that's my question - what can I use to keep that info so I can mail it to myself in an error email? I have two tasks that are connected to the For Loop - success and failure. At this point, they just send a "job success" or "job fail" email. Since I'm passing parameters around, could I email myself the value of the variable?
What I did was before the problem dataflow, I put in a sql task that throws select * from tablename where 1=0 and it succeeds, but the select * from tablename where create-date > SYSDATE - 1 in the dataflow below it fails. Isn't there a way for me to just keep going on failure? The dataflows are all separate tables, so even if I do have a problem, I'd like to keep track of what messed up and continue.
I guess the best solution would be to have a buffer of some sort that I record my facility ID in along with the dataflow name and then when everything finishes, mail that to myself.
-
Matthew Cushing (3/20/2009)
The issue is in the datareader, so going out to the progress db is an issue. When using For Loops, is there a certain number of connections I can make or something like that causing an issue?None that I know of.
CozyRoc (3/20/2009)
If not, direct the failure to log the error and continue execution.
I guess that's my question - what can I use to keep that info so I can mail it to myself in an error email? I have two tasks that are connected to the For Loop - success and failure. At this point, they just send a "job success" or "job fail" email. Since I'm passing parameters around, could I email myself the value of the variable?
You can certainly email the value of variable. I would recommend that you create a variable and a script task, which joins all error information in this information. Then send the information in one email. You don't want one email for each failure.
What I did was before the problem dataflow, I put in a sql task that throws select * from tablename where 1=0 and it succeeds, but the select * from tablename where create-date > SYSDATE - 1 in the dataflow below it fails. Isn't there a way for me to just keep going on failure? The dataflows are all separate tables, so even if I do have a problem, I'd like to keep track of what messed up and continue.
I guess the best solution would be to have a buffer of some sort that I record my facility ID in along with the dataflow name and then when everything finishes, mail that to myself.
You may have other kind of problem. Try to execute the failing query to find out if it runs properly.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply