Hello,
Today is day two of using SSIS and having some issues adjusting. In DTS I would simply populate my global variables via an Active X step or pass them in at the job line.
I am having a hard time being able to do either or in SSIS. Really all I want to be able to do is pass in a server name to the Connection of an execute SQL task from a scheduled job line.
Same goes for any variable really ... how do I pass them in, and how do I assign them to specific areas of a task?
Thanks!
Mr.Bean wrote:
Hello,
Today is day two of using SSIS and having some issues adjusting. In DTS I would simply populate my global variables via an Active X step or pass them in at the job line.
I am having a hard time being able to do either or in SSIS. Really all I want to be able to do is pass in a server name to the Connection of an execute SQL task from a scheduled job line.
Same goes for any variable really ... how do I pass them in, and how do I assign them to specific areas of a task?
Thanks!
If you want to pass values into the package from the command-line then use the /SET option of dtexec.exe.
Does that answer the question?
By the way, the most analogous thing to populating global variables from an ActiveX Script is to do it in a Script Task. Don't use the ActiveX Script in SSIS - it isn't useful. The following post explains how you do it using a script task:
Writing to a variable from a script task
(http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx)
Get all of that working first and then we can address "how do I assign them to specific areas of a task" (short answer - use expressions).
-Jamie
|||
By the way, if you're finding the transition from DTS difficult I've got some documentation that may help:
The mind-shift from DTS
(http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1396.aspx)
But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)
DTS Connections and SSIS Connection Managers
(http://blogs.conchango.com/jamiethomson/archive/2006/04/02/3422.aspx)
The new ETL Paradigm
(http://blogs.conchango.com/jamiethomson/archive/2005/02/15/1002.aspx)
-Jamie
|||So now what I'm reading here ...
http://www.databasejournal.com/features/mssql/article.php/3509601
DTExec /FILE SamplePackage.dtsx /SET \Package.Connections[ConnectionID].InitialCatalog;tempdb
Is really the intended way to pass in server names? What if (like in several of my processes) I connect to multiple servers for different steps? I really have to retrieve the GUID of those tasks and populate them appropriately like above?
I'm just now starting to look into the Package Configurations ... not sure what I'm doin here just yet, but would this be a better solution?
I miss DTS ...|||
So now what I'm reading here ...
http://www.databasejournal.com/features/mssql/article.php/3509601
DTExec /FILE SamplePackage.dtsx /SET \Package.Connections[ConnectionID].InitialCatalog;tempdb
Is really the intended way to pass in server names?
Someties yes. Sometimes no. Depends on your scenario.
What if (like in several of my processes) I connect to multiple servers for different steps?
Then you'll have multiple connection managers.
I really have to retrieve the GUID of those tasks and populate them appropriately like above?
Not sure I understand what you mean. You never have to know about task GUIDs.
I'm just now starting to look into the Package Configurations ... not sure what I'm doin here just yet, but would this be a better solution?
Possibly. Depends on your scenario. Most people find that configurations are the way to go rather than passing in connectoin details from the command-line.
I miss DTS ...
Well there is a steep learning curve with SSIS but most people find it worth it when they get there. Configurations, for example, are wonderfully powerful and remove many of the headaches people have in DTS.
-Jamie
|||Many of my processes (including this one) are designed to be very generic so that I can dynamically set specific items ... such as server name, database names, etc.
So the current SSIS package I'm working on, I have built and ready to go, but am struggling to find the best way to allow for dynamic variables (server/dbnames). Right now everything is static and it works ... but I need to change that.
Just built my first package configuration, and this might be what I'm looking for? By selecting which tasks I want put into this configuration (such as variables and connections), this will properly update those areas?
My wording isn't the greatest ... as I said, day two and not doing so well with this yet.
Thanks again|||
Many of my processes (including this one) are designed to be very generic so that I can dynamically set specific items ... such as server name, database names, etc.
So the current SSIS package I'm working on, I have built and ready to go, but am struggling to find the best way to allow for dynamic variables (server/dbnames). Right now everything is static and it works ... but I need to change that.
Just built my first package configuration, and this might be what I'm looking for? By selecting which tasks I want put into this configuration (such as variables and connections), this will properly update those areas?
My wording isn't the greatest ... as I said, day two and not doing so well with this yet.
Thanks again
It sounds as though you want a generic package. Something that you can execute for multiple environments and effectively "parameterize" it on each execution. Is this correct? In this scenario you will probably want to pass in values using the /SET option of dtexec.exe. By the way, make it easy on yourself, pass in the value for the ConnectionString property, not a seperate property for ServerName/Catalog etc...
Configurations are more suited to moving packages through your dev/test/UAT/live environments.
-Jamie
|||Yeah that is more of what I am looking for ... so I guess that brings me to my next question.
How do I do that? For example ...
I have a variable called DBList. I want this variable to have database names passed in, what would my /SET command look like?
Then for the connections. Say I have a SourceServer and DestServer variable. Same question above applies here, but how do I bind a variable to the connection in the properties of a task?
Thanks again for all your help.|||
Yeah that is more of what I am looking for ... so I guess that brings me to my next question.
How do I do that? For example ...
I have a variable called DBList. I want this variable to have database names passed in, what would my /SET command look like?
Then for the connections. Say I have a SourceServer and DestServer variable. Same question above applies here, but how do I bind a variable to the connection in the properties of a task?
Thanks again for all your help.
You don't. You can reference the ConnectionString property of the connection manager directly from the command line. So your command-line would look something like:
dtexec.exe /FILE "C:\Package1.dtsx" /SET "\Package.Connections[conn_name].Properties[ConnectionString]";SomeValue
-Jamie
|||
Mr.Bean wrote:
Yeah that is more of what I am looking for ... so I guess that brings me to my next question.
How do I do that? For example ...
I have a variable called DBList. I want this variable to have database names passed in, what would my /SET command look like?
Then for the connections. Say I have a SourceServer and DestServer variable. Same question above applies here, but how do I bind a variable to the connection in the properties of a task?
Thanks again for all your help.
You don't. You can reference the ConnectionString property of the connection manager directly from the command line. So your command-line would look something like:
dtexec.exe /FILE "C:\Package1.dtsx" /SET "\Package.Connections[conn_name].Properties[ConnectionString]";SomeValue
-Jamie
Sorry for all the questions ... but I'm not sure I follow you on that.
http://img382.imageshack.us/img382/3171/ssisconnectionpropsbx5.jpg
As you can see the connection at this execute SQL task has no specific name to it. This is the connection I want to be able to update dynamically. So are you stating to create a new connection to a server, and simply update that per your /SET example?
dtexec.exe /FILE "C:\Package1.dtsx" /SET "\Package.Connections[SERVERNAME].Properties[ConnectionString]";SERVERNAME
Thanks again|||
Jamie Thomson wrote:
Mr.Bean wrote:
Yeah that is more of what I am looking for ... so I guess that brings me to my next question.
How do I do that? For example ...
I have a variable called DBList. I want this variable to have database names passed in, what would my /SET command look like?
Then for the connections. Say I have a SourceServer and DestServer variable. Same question above applies here, but how do I bind a variable to the connection in the properties of a task?
Thanks again for all your help.
You don't. You can reference the ConnectionString property of the connection manager directly from the command line. So your command-line would look something like:
dtexec.exe /FILE "C:\Package1.dtsx" /SET "\Package.Connections[conn_name].Properties[ConnectionString]";SomeValue
-Jamie
Sorry for all the questions ... but I'm not sure I follow you on that.
http://img382.imageshack.us/img382/3171/ssisconnectionpropsbx5.jpg
As you can see the connection at this execute SQL task has no specific name to it. This is the connection I want to be able to update dynamically. So are you stating to create a new connection to a server, and simply update that per your /SET example?
dtexec.exe /FILE "C:\Package1.dtsx" /SET "\Package.Connections[SERVERNAME].Properties[ConnectionString]";SERVERNAME
Thanks again
Hmmm...seems to be a lack of understanding here. Tasks contain no knowledge about the sources they ultimately connect to. They have a reference to a connection manager - and THAT is where the details of the external data source (be a it a file, a database or whatever) reside.
In other words - forget about the task. You have to update the the ConnectionString property of the connection manager being used by the task.
-Jamie
|||I think I follow you on that ... but the fact is that I have to create a connection in the connection manager for the task to refrence correct? So that means I still have to set a static connection to a server in the connection manager as it does not allow me to create a blank OLE DB connection that can be populated from the command line (which is what I want to do), right?
Sorry for the confusion here.|||
I think I follow you on that ... but the fact is that I have to create a connection in the connection manager for the task to refrence correct?
Correct.
So that means I still have to set a static connection to a server in the connection manager as it does not allow me to create a blank OLE DB connection that can be populated from the command line (which is what I want to do), right?
Sorry for the confusion here.
No need to apologise!
You do need to set the ConnectionString property of the connection manager to SOMETHING, yes. You may say "Why do I need to do that if I'm going to change it anyway when I execute?" I think that's a legitimate complaint however I don't see it as a problem. If it IS a problem then you can put BIDS into offline mode by selecting 'Work Offline' on the 'SSIS' menu.
Hope that helps.
-Jamie
|||I see ... makes sense.
So in summary, does this looks about right?
/FILE "C:\Directory\Package.dtsx" /SET "\Package.Variables[User::DBList].Value";"“DB1, DB2, DB3“" /SET "\Package.Connections[SERVERNAMESRC].Properties[ConnectionString]";SERVERNAMESRC
/SET "\Package.Connections[SERVERNAMEDEST].Properties[ConnectionString]";SERVERNAMEDEST
Does the command line look right? Do I have to specify each /SET?
Thanks again for all your help, definitely made this easier and made me realize its time to sit down and do some reading!
|||
I see ... makes sense.
So in summary, does this looks about right?
/FILE "C:\Directory\Package.dtsx" /SET "\Package.Variables[User::DBList].Value";"“DB1, DB2, DB3“" /SET "\Package.Connections[SERVERNAMESRC].Properties[ConnectionString]";SERVERNAMESRC
/SET "\Package.Connections[SERVERNAMEDEST].Properties[ConnectionString]";SERVERNAMEDEST
Yes, it looks right. Only you can tell if it is or not. Note that the value you supply will have to eb a full connection string. check out http://www.connectionstrings.com or just cop and paste from those that you set up in the package and edit accordingly.
Does the command line look right? Do I have to specify each /SET?
Yes.
Thanks again for all your help, definitely made this easier and made me realize its time to sit down and do some reading!
No getting around that I'm afraid :)
By the way if you get to sorting this out I would appreciate your marking one of my posts as an answer. The search function takes that into account when it delivers search results.
-Jamie
No comments:
Post a Comment