May 28, 2008 at 11:13 am
Can anyone please tell me how to loop through the connections in SSIS? In DTS I used to loop through the connections for output files in order to rename them with the current date added to the connection name. I used to set DTSGlobalVariables.Parent.Connections.Item(i).Name for each item in DTSGlobalVariables.Parent.Connections, but I don't know how to do it in SSIS. I've been Googling and looking through other posts in this forum, but I haven't seen anything that addresses this issue.
Thanks
Elaine
May 28, 2008 at 4:38 pm
Are you asking how to dynamically set the connection string or how to loop through a directory of files?
Ben Sullins
bensullins.com
Beer is my primary key...
May 29, 2008 at 7:02 am
There are about 12 different flatfile connections in addition to an odbc connection. I would like to rename each text file when the package is run - for example, adding the current date to the output file name. I want to know how to loop through all the connections in the connection manager to rename them when the package runs. I used to do this with DTSGlobalVariables.Parent.Connections in dts, but I don't know what to use in ssis.
Elaine
May 29, 2008 at 11:59 pm
Use a for each ADO enumerator.
Now you can write a query to get your connection strings, and pass this to an object variable.
Good luck
~PD
May 30, 2008 at 1:59 pm
Thank you!
April 14, 2009 at 12:00 pm
Can you elaborate on how to do this please? I have a similar issue, and I just recently started using SSIS.
May your fish always be bigger
Than the holes on your net.
April 14, 2009 at 2:59 pm
There is probably a more elegant way to do this, but this is what I ended up using, and it works for my report. Inside the script task I do this:
dim filepath as string
dim i as integer
filepath = an output path variable that I had passed into the ssis package with a date string that is part of the naming convention of the output files
For i = 0 To Dts.Connections.Count - 1
If Dts.Connections.Item(i).ConnectionString.Contains(" some common string such as part of the directory for the connections in the package that I want to set the name ") Then
Dts.Connections.Item(i).ConnectionString = filepath & Dts.Connections.Item(i).Name & ".txt"
End If
Next
April 15, 2009 at 5:46 am
Thanks!
May your fish always be bigger
Than the holes on your net.
April 15, 2009 at 6:58 am
I wound up using:
Dim strConnect As String
strConnect = "Data Source=" + Dts.Variables("User::currentserver").Value.ToString + ";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
Dts.Connections(" ").ConnectionString = strConnect
I'm trying to retrieve database settings, such as recovery model (and a few others) for each database, for each SQL server and store the results in a central database, so it can be easily viewed. The server name is the first foreach loop. The databases collection is the second foreach loop. For each iteration of each loop, the server and database values are stored in global package variables. I'm able to cycle both loops successfully, I'm now stuck on the SQL task that returns the settings (recovery model) value:
------------------------------------------------------------------
DECLARE @QUERY AS VARCHAR(200)
DECLARE @OR AS VARCHAR(1000)
declare @recmodeltbl table(recovery_model varchar(100))
insert into @recmodeltbl select convert(varchar(100),DATABASEPROPERTYEX (@dbs , 'Recovery')) as recovery_model
select @recovery_model = recovery_model from @recmodeltbl
------------------------------------------------------------------
under Parameter Mapping for the SQL task I mapped
Dts.Variables("User::currentserver") to @server as input
Dts.Variables("User::currentserver") to @dbs as input
Dts.Variables("User::recovery_model") to @recovery_model
I get: [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".
I have tried single result set and full result set. Any ideas?
May your fish always be bigger
Than the holes on your net.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply