Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

SSIS & Environment Variables Expand / Collapse
Author
Message
Posted Wednesday, August 11, 2010 12:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 26, 2012 11:11 AM
Points: 21, Visits: 79
I appreciate that... thank you.
xp_cmdshell was the hint I needed...
I ran that and low and behold there's that value that keeps haunting me.

Bouncing the SQL Service was my next step, was going to do it first thing this morning before everybody got to work but my boss told me I had to have the Windows admins do it, even though its dev.

I'll get to work on scheduling the SQL bounce.

I think Environment Variables are great if you don't have to ever change them. And that was our intent when we first started using them.
The thing w/ Config files is that the deployment utility tries to package them up and if the DBA doesn't hack the Manifest to not push the config file, you overwrite your prod config. We use config files for data related variables. But we don't want app devs even knowing where their production SQL server is. Ideally using environment variables to store SQL Server names would let us (DBAs) promote app dev developed pacakges to QA and Prod with out having to change a single line of code or config file. But since we hosed our QA SSIS server and could run pacakges from it anymore, our work around was to run QA and dev pacakges from our dev SSIS server, hence the env var value shell game.

Anyway... thanks for the help gang. I'll follow up once I get the SQL service restarted
Post #967733
Posted Friday, January 04, 2013 10:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 11:46 PM
Points: 26, Visits: 56
Hi Andrew,

I have a problem in using SQL table for package configuration.Let me explain in detail.
I have the task Flatfile source---->(correct output) to oledb destination
Flatfile source---->(error output) to flatfile destination
This is the package task.
Now I have Servername,Databasename,flatfilesource name,flatfile destination name
These are all the connection strings which are placed in SQL table(SSIS_Configurations)
But when I need to change the database name and flatfilesource name,I updated at the backend SQL table((SSIS_Configurations).
But when I open the package it is still pointing to the old database and flatfilesource name.

How should I proceed further?? It's very urgent.Please help.
Thanks in advance.

-Nisha
Post #1403012
Posted Friday, January 04, 2013 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 12:43 PM
Points: 7, Visits: 51
When you save your package from BIDS, the values you have for all properties are saved in the DSTX.
If you have package properties stored in your SSIS Config table, those package properties are overwritten at runtime by the values stored in the table... The DSTX stored in MSDB, or wherever, never changes. So when you pull your package down from your server, you are seeing what was deployed, which was the DSTX you saved from BIDS.

Part of the Runtime, pre-execution phase of your SSIS pacakge is to step thru your various package configurations substituting the property value in the package with that stored in your SSIS Config table, for example.

Are you finding that when your package runs, your select properties aren't being updated to your table stored values?
Post #1403030
Posted Friday, January 04, 2013 11:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 11:46 PM
Points: 26, Visits: 56
Thanks for your reply..

If I need to change the Db name and Flatfile source name to the package (which is not deployed yet)
can I update the SSIS_config table at the back end???
I tried it by updating it at the back end,but when I opened the package I need to do the changes manually,So when I do it manually in BIDS te table is updated.But when I update at the back end its not reflecting in BIDS.
So what should I do to do that automatically.Because I have 100 packages which needs to be changed often.(i.e; Database name and Flatfile source name)
Post #1403032
Posted Friday, January 04, 2013 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 12:43 PM
Points: 7, Visits: 51
My knee jerk reaction is to say "Deploy your packages". Just create a dev folder on your SSIS server and plop them in there. Its pretty easy to deploy 100s of packages using the deployment utility.
Any reason I can think of to not deploy them and run in design mode in BIDS is negated by your volume of packages.

even so... Running in design mode mimics runtime, so if you are in BIDS, you can see that your Connection is pointed @ Server ABC and you know that your package config is set to server XYZ, then wen you click Run in BIDS, Pre-Execution will go pick up the value from your config table and use it.

What I'm not sure about tho is, since you are running in design mode, if you DSTX that is open in BIDS gets overwritten with the config table values. So in the case of the above example, after completion is your packakge that you have open in BIDS showing server XYZ, or is it still ABC?

I can't remember.

If the above doesn't mae sense or does't work out... you can put your 100+ DSTX files in a folder and user a text editing tool do a masive Find/Replace on server ABC changing it to XYZ, then open each of those DSTXs and run as you normally do.. But I advise against tinkering w/ DSTX directly.
Post #1403037
Posted Friday, January 04, 2013 11:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 11:46 PM
Points: 26, Visits: 56
The SERVER NAME Is same only my database name and flatfile source path needs to be changed every time.So the answer to your Question is When I change in config table, the changes are not seen in BIDS.
So my DTSX package is still pointing to the old Database name and old flatfile source path ..It's not PICKING the new Database name and old flatfile source path found under Config table.
Post #1403049
Posted Friday, January 04, 2013 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 12:43 PM
Points: 7, Visits: 51
I was Using ServerName as an example, remember, Package configs can be used to manipulate any package property.

Lets look @ it this way.
We're dealing with two spheres of SSIS: one being Runtime, the other being Design Time. When you are clicking and dragging and typing and saving in BIDS, that is design time. When you click that green triangle you enter runtime.

In design time, you can see that your database name is set to ABC.
The value for your database name in you config table is set to XYZ.

When you run the package, run time, ie clicking te green triangle, where is the work done? where is the database action taking place? ABC or XYZ.
If your Package configs are configured correctly, it should be XYZ.

When troubleshooting SSIS I ALWAYS reccoment isolating the issue, ie start over with a brand new blank package. In your case you are struggling with Package configs. Personally, I would create a new package, stick a email task in it, with all appropriate requitements (SMTP connection, etc). Send the email to your self, in the subject put "DSTX Value". Run the package, make sure you get the email.

Now add in a package configuation. So create a connection to where your config table is, and go thru the steps of setting up a package config via the Package Config dialog. map the config to the subject line of your email task. Complete the Package Config set up so that your record is saved to your config table.

Go find your new config table record. Notice the Configured Value is 'DSTX Value'. Update this record setting configured Value = 'Config Table Value'.
Go back to BIDS and run your package again. Make sure you get the email, the subject should be "Config Table Value". If its not, scan your output window for warnings and errors.
Post #1403062
Posted Friday, January 04, 2013 12:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 11:46 PM
Points: 26, Visits: 56
Thank you so much for your time and patiece.Will follow your suggestions and let me check.Will update you once I am done.

Post #1403073
Posted Tuesday, January 08, 2013 3:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 11:46 PM
Points: 26, Visits: 56
Thank you so much.I figured out the problem.The package path in the back end SQL table was not updated correctly.

Thank you so much for your suggestions.
Post #1404460
Posted Thursday, January 10, 2013 11:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 11:46 PM
Points: 26, Visits: 56
Jeff,

I have one more question.
I have a derived column expression like this :

LTRIM(Column1) == "" ? (DT_STR,100,1252)NULL(DT_STR,100,1252)
(Column1== "Y" ) ? 1 : 0

This Column1 is a 'BIT' datatype. So I have to write an expression for validating :if the Column1 comes with empty string replace it with 'NULL' and if it comes as "Y" then 1 else 0
Those above expression works well independently.

How to write this two condition in a single expression??

Please help.

Thanks
Post #1405588
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse