September 1, 2015 at 4:56 am
Ok quick scenario,our primary file server is changing between today (Sep 1 2015) & the next 6 months.
On this file server we have a share where we put flat files that we process using SSIS.
So we are going to change the packages to work with dynamic flat file destination/sources.
We're putting the 'share' location in a SQL table called SSIS_Parameter.
Now we have a test package,works fine,however we were wondering whether or not the dynamic destination was actually rendered @ runtime.
We found it wasn't,since we had put the actual share name in the variable,it looked like it was changing at runtime but it wasn't.
So changing it to an non-existing location broke the package, changing the share variable to "C:\" and the value in the table to "\\sharename\",seem to work.
Now I don't need a solution,just a second opinion on this.
September 1, 2015 at 11:57 pm
Resender (9/1/2015)
Ok quick scenario,our primary file server is changing between today (Sep 1 2015) & the next 6 months.On this file server we have a share where we put flat files that we process using SSIS.
So we are going to change the packages to work with dynamic flat file destination/sources.
We're putting the 'share' location in a SQL table called SSIS_Parameter.
Now we have a test package,works fine,however we were wondering whether or not the dynamic destination was actually rendered @ runtime.
We found it wasn't,since we had put the actual share name in the variable,it looked like it was changing at runtime but it wasn't.
So changing it to an non-existing location broke the package, changing the share variable to "C:\" and the value in the table to "\\sharename\",seem to work.
Now I don't need a solution,just a second opinion on this.
Since you have not gone into detail about exactly how the 'dynamic destination' is being set, I cannot help with any specifics.
But it is most certainly possible to do. All of our flat file source and destination paths are dynamically controlled from SSIS Environments using parameters. I know that you said that you did not want a solution, but if you're on SSIS 2012+, I would think very hard before ignoring SSIS environments and rolling your own solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 2, 2015 at 4:51 am
Environment:
*Windows Server Enterprise
*Microsoft SQL Server 2008 r2
*Microsoft Visual Studio 2008 (BiDS)
Scenario
Several application where we either can not directly connect to with SQL server or we won't get permissions for will place either csv or properly formatted txt files in a share on our primary fileserver (lets call this pfs0001)
Each file goes to a folder on that share that is named after the application from which the file is coming.
We currently have 1 or more ssis packages per application to import the data from the csv files into our sql databases.
Currently these locations are 'hardcoded' into the ssis packages,the hardcoding is the folder location & the filename,which we get using a for loop or script element.
For example \\pfs0001\XYZ\abc.csv
Now in our example package we split the variables further into share,folder name and file name.
Share \\pfs0001Folder XYZ
filename abc.sv
Now we collect the share value from a parameter table.
What we noticed if we keep the values for folder default pfs0001 then the file would arrive there but the value from the table gets ignored.
If we changed the share default value to a non-existing share the package would not run/build.
If we changed default value for share to C:\ the package will run\build and the file will arrive at the folder in the share. (Since the value in the table is \\pfs0001\)
Is this the correct way to do this in a 2008 enviroment.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply