June 30, 2009 at 11:46 am
Can someone supply me the .net script to dynamically set the value of 2 package variables at runtime -- sourcing the 2 data values from a SQL table?
I'm NOT a .net developer and I'm struggling w/ this.. perhaps this will help me better understand .net scripting
If there is a way to dynamically set these variables in a stored proc, that'd be a great sample too -- as I've coded hundreds of procs
The package variables, concatenated together, represent an XLS spreadsheet. (Once the variables are populated via the .net script, I'll concatenate them via an Expression in the Pkg).
My source data is located in SQL table: FileLocations with the following Column names & sample data:
colname = Directoryname
value = \\MyServer\MyDirectory\MySubDirectory\
colname = FileName
value = MyFileName.xls
My 2 package variables are called: DirName -and- FilName
therefore the script needs to perform a connection to the DB then:
SELECT Directoryname FROM FileLocations --> and populate Pkg variable DirName
SELECT FileName FROM FileLocations --> and populate Pkg variable FilName
thx in advance...
June 30, 2009 at 11:57 am
I think you are going about this the wrong way.. You don't need to do it that way..
Turn on Package Configurations by right clicking on the Control Flow and choose Package Configurations, the click on the check box to turn them on, then click add, chose SQL Server as the source.. Later you will chose what variables to set..
This will set the values at runtime when the package starts. Letting SSIS do all the hard work.. You could later us .net to append the values together or use a sql task. Whatever is easier for you..
CEWII
June 30, 2009 at 12:04 pm
Actually, I am well versed in the use of Package Configurations using a .dtsConfig file housing variables which match my Package variables.
I typically pass hardcoded values (Connections, Provider strings, Directory Names, etc.) to the pkg via this mechanism.
Now I have a situation where I want to use the same 2 "generic" package variables to accomodate dozens and dozens of DirectoryName / FileName combinations...
I was under the impression that rather than hard-coding 1-to-1 dtsConfig variables-to-SSISPkg variables, I could dymamically reset the contents of my 2 Package variables (representing DirectoryName and FileName) prior to each Data Flow task which Loads my XLS into my table...
June 30, 2009 at 1:40 pm
I get where you are going but I'm not suggesting config files, there is a SQL Server option at the same place.
I think you could accomplish it this way:
1. Use an EXEC SQL task to get the filename and path by setting the result set to Single Row and mapping the result set into those 2 variables.
2. Concatenate those two together using whatever method you like and build connection string.
3. In the connection manager for your source set the expression for connection manager to be the calculated connection string variable.
CEWII
July 1, 2009 at 6:39 am
Just as a side point, in most cases you don't need .net script to set package variables.
Use expressions where possible as they are easier to debug, more visible, and can be evaluated at design time. Further more they simplify your package logic as you don't have unnecessary script tasks littering your control flow.
Kindest Regards,
Frank Bazan
July 1, 2009 at 8:26 am
thx Elliott -- still having an issue & hoping you can help me out:
I used your suggestion to create a couple of pkg variables, populate them indidually via EXEC SQL tasks (settting result set to Single Row) then concatenting the variables together as an Expression to derive the Connection Manager connection.
Question on the mapping of te Single Row output to the Variable:
In the SQL Task, I add my SQL Select Statement (eg. SELECT BaseFolder from FolderTable... ) then under 'Parameter Mapping' I have several values to manipulate:
- Variable Name (I set to my predefined VaribableName: FolderName)
- Direction (I set as OUTPUT)
- Data Type (I set as NVARCHAR)
- Parameter Name (I did not touch; left ast NewParameterName)
- Parameter Size (I did not touch; left as -1)
(Does the preceding look correct to you?)
I also set DelayValidation to TRUE on my Connection Manager connnection (the ConnMgr name I use is ExcelConnection)
In the ConnectionManager properties for the Connection called ExcelConnection, I set the properties Expression to concatenate the multiple variables together (including the Provider string for Excel) and when I initially open the Package, I receive ERRORS for the Data Flows saying 'Validation Error... an OLE DB error has occurred.. )
Looks like the package is validating the Connections BEFORE the variables are actually populated via the SQL Tasks -- and therefore giving me errors.. How do I ge around this?
July 1, 2009 at 9:10 am
Parameter mapping is on the input, ie passing variables TO the query, the next item down is result set, it maps output FROM the query to variables. You can use PArameter mapping to control your where clause and such but the result set will be where you map the results back into your process.
CEWII
July 1, 2009 at 9:17 am
yep.. proactively determined & corrected that.. I now have NO paramater mappings. I setup my Result Set to include the Variable Names...
Still -- I receive the Connection Manager error..
It's trying to resolve the Connection string before it is actually built .. therefore throwing errors when I open the SSIS pkg..
thoughts
July 1, 2009 at 9:30 am
If you run the package outside of BIDS does it work? Or does it still error out?
Did you set delayed validation at the package level or just the component level?
When the package is opened BIDS verifies the whole thing. You might want to point that connection at a place where you know a file exists.
CEWII
July 1, 2009 at 9:51 am
Elliott - thx. (great minds think alike) it worked...
prior to your feedback, I actually did default the newly created variable 'values' to actual valid values (Dir name, File name, etc) and it passed the validation test...
I then UPDATED my SQL table to contain bogus values.. ran the pkg and, as I expeceted, it failed on the bogus DIR name.. then UPDATEd my SQL table w/ Valid DIR name and the pkg ran successfully.. Proving the SQL task is hitting the DB, populating the variables on the fly, and working great..
thanks for you input .. very much appreciated !!!
bt
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply