November 3, 2006 at 12:19 pm
Hi David,
Lee posted this question in 2 threads, so I answered it in the other thread. However I was curious about the following statement that you made.
"One of the big advantages to ssis over dts was that they would be more portable - and this was partially because the connection information is not stored in the package. We didn't find them to be quite as portable as MS said... and we SCRAPPED the project... the ssis piece, at least..."
Did you trying creating a .dtsConfig file? And what limitations did you encounter when you used it? So far, I have been able to use to deploy my packages to different servers, however I've only been the setting the InitialCatalog and ServerName properties. So I'm wondering if there are limitations that I haven't encountered yet.
Here's the XML format of my .dtsConfig file.
<?xml version="1.0" ?>
When I change the ConfiguredValues for my ServerName and InitialCatalog properties, the package runs without any errors on any server to which I deploy (at least for now).
"One last thing before I go home. If you create a new job and create a step and you set the Type = SQL Server Integration Services Package, what is the Data Sources tab suppose to do for you..."
I've been trying to break SSIS for the last couple of months, so I've been trying out a large combination of things.
One of the things that I've tested is using a job to execute a pkg, and creating a pkg with variables that would be populated at run-time by that job.
I was able to do both things successfully.
To answer the first question, I’ve found that the Data Sources tab in the Job Task is simply a list of all the data connection managers in your package. I don't know what Microsoft's intention for this feature is, but I use to confirm that the deployed package contains all of the Connections that I defined (for ex, I have a package with 25 connections and I use the Data Sources tab to verify that all 25 were picked up when I deployed the pkg).
The best thing about the Job task in SQL Server 2005 is that I can use it to populate the variables in my pkg at run time.
For example, I have 2 variables in my pkg: one for store (varStore) and one for enterprise (varEnterprise). I have several stored procedures in my pkg that need those two variables to be populated so that they can in turn populate the parameters in the stored procs (@Store and @Enterprise) thereby permitting the stored procs to return the correct data to the Data Flow Components.
To get the Job to populate the pkg variables at run time, I’ve done the following:
In the Job Step Properties window, I click on the Set Values tab.
1) In the Property Path column, I enter the following info for the variable:
\Package.Variables[User::varEnterprise].Properties[Value]
2) In the Value column, I enter the value for the variable. For ex, MMI. There's no need for single quotes even though this is a string.
I repeat steps 1 and 2 for every variable that I need to populate in my pkg.
When I'm done, I click on the Command Line tab in order to verify that the pkg contains the information for my variables.
For ex. my Command Line Sql shows the following:
/SQL "\StoreLevelImportConfig" /SERVER "AD***\KADDIE2005" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package.Variables[User::varStore].Properties[Value]";"BakersGoods" /SET "\Package.Variables[User::varEnterprise].Properties[Value]";MMI
This is the code that the Job executes when it runs the pkg (or I could run this from the CMD prompt) and it successful populates my variables which in turn pass those values to my stored procedures.
November 6, 2006 at 1:02 pm
Thank you for replying Scott, David, and Abdul. As I said in a previous post, we are looking to store package configuration data in the .dtsconfig file rather than in a SQL table. The whole reasoning for storing it in a SQL table was to protect it and to have the data backed up. But we can say the same with the .dtsconfig file.
I too am not sure what MS's intention was for the various options in an SSIS job step. But, if you reread my posts, my take on this was not what I thought. I would have thought the job step would take effect first and then the package configurations would take effect in the order they were specified. But what I saw was not true. If my package configuration affected one connection object in the package and the job step was to affect a totally different connection object, then the connection object the job was to affect did not fire first. Oh well.
Thanks again for your posts and thoughts on this issue.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply