Hey Guys,
I've been tasked with doing a task where I connect to a number of different sql servers, pull audit logs, and combine them into one table on our DBA instance. I've seen MANY posts about this online, read through, followed the steps that I can mirror on my own here but it doesn't seem to be behaving the same as everyone elses I see posting online and I was wondering if I could get some help.
So Step 1: I pull the data from my table of Servers/Instances from a SQL Server Table
Step 2: I use a for each loop using Foreach ADO Enumerator and use the variable mappings to populate both a connection string variable and a server variable. NOTE: THIS WORKS The variables get populated and change through every iteration.
Step 3: Set the dynamic connection managers expression to use the server variable as Data Source and ConnectionString with the connection string variable this does not work. Even though the variables are updating, the connection string never does. The Data source stays blank.
Note 1: I've attempted this with connection string parameterized and with data source parameterized or with either one, none of it seems to populate in the connection manager once the package is started.
Note 2: Initial catalog is static so there's no need to modify it. I do have it assigned to a variable though in my testing and it does populate normally, I assume this is because its set at the start of the script.
Based off the note above, I need to somehow force the connection string to recompile on each loop. Any ideas on how to do this?
EDIT: Delay validation is set to true for the package.
Ok how I've done this in the past.
Create a variable of type "Object" to store the ADO.NET connection string table
Create a variable of type "String" to store the individual connection string
Create an "Execute SQL Task" as to pull the fully built connection string only from the source table of what to loop over
From this task, dump the result set to the variable of type Object
Have a "For Each Loop" for "Foreach ADO Enumerator" source variable is the "Object" variable, "Rows in the first table"
On the loops variable mapping, map output to the "String" variable
On your connection manager, set the connection string expression to be the "String" variable
January 20, 2020 at 3:52 pm
Ok how I've done this in the past.
Create a variable of type "Object" to store the ADO.NET connection string table
Create a variable of type "String" to store the individual connection string
Create an "Execute SQL Task" as to pull the fully built connection string only from the source table of what to loop over
From this task, dump the result set to the variable of type Object
Have a "For Each Loop" for "Foreach ADO Enumerator" source variable is the "Object" variable, "Rows in the first table"
On the loops variable mapping, map output to the "String" variable
On your connection manager, set the connection string expression to be the "String" variable
I believe I have all of those things covered.. see below:
CREATE TABLE [##ConnString]
(ConnString nvarchar(500),
Server NVARCHAR(125))
INSERT INTO ##ConnString
SELECT
CASE --Create Connection String for connection string variable
WHEN s.Server <> i.InstanceName
THEN CONCAT('Data Source='+s.Server, + '\' + i.InstanceName +';Initial
Catalog=Database;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;')
ELSE CONCAT('Data Source=',+ s.Server +';Initial Catalog=Database;Provider=SQLNCLI11.1;Integrated
Security=SSPI;Auto Translate=False;')
END AS ConnString,
CASE --Get server name for server variable
WHEN s.Server <> i.InstanceName
THEN CONCAT(S.Server,+'\'+ i.InstanceName)
ELSE s.Server END AS Server
FROM dba.Servers AS s --Lists all servers
INNER JOIN dba.Instances AS i ON i.DBSID = s.DBSID -- joining instances and servers
SELECT * FROM ##ConnString
I run execute sql task with this and set the result set to my user::ConnectionStringTable (object variable)
The rest is covered by screenshots below..
Okay.. Never Mind. The issue was something different, apparently SSIS is just a liar and wasnt showing me the connection string was updating while it actually was and a different error entirely was preventing it from working. Thanks 🙂
January 21, 2020 at 8:25 am
IIRC you can only store one item in the object variable, so it has to be the connection string or the server.
I tried with server and catalog and it wouldn't work, (happy to stand corrected), so you would only want to pull out the fully built connection string from the table and use that for looping
January 21, 2020 at 1:39 pm
I was able to store multiple values from my sql server table in the object variable, I'm currently storing: Connection String, Server Name, Server ID and Instance ID.
In reality the only thing I was doing wrong was making the assumption that during debug mode I would be able to see the connection string update.
January 21, 2020 at 2:14 pm
Well I will go back and re-look at my code see if I can perfect it in anyway to do ADO and OLE connections strings, always nice to have multiple streams.
As for the debug, yeah that's something I hit a snag with, adding a watcher to the variable change event let me see it in the watcher window and I could see the variable changing
January 21, 2020 at 5:06 pm
Yeah, I just finished it, added checks for the conncetion strings to make sure it can connect and if it cant to log what it cant connect to. I did run into another snag where it said it was truncating an id and I was literally like THE ID IS 10 HOW ARE YOU TRUNCATING 10 WTF, turns out when it made it a string it set the length to 1... took me longer than I would like to admit to find that. Had to go into the dataflow task and actually modify the output columns.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply