January 4, 2013 at 11:59 am
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.
January 4, 2013 at 12:23 pm
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.
January 4, 2013 at 12:43 pm
Thank you so much for your time and patiece.Will follow your suggestions and let me check.Will update you once I am done.
January 8, 2013 at 3:09 pm
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.
January 10, 2013 at 11:57 am
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
January 11, 2013 at 1:56 am
If Column1 is bit, it won't be an empty string. It'll either be 1 or 0 or NULL.
John
January 11, 2013 at 7:13 am
Yeah, I'm kinda confused too.
So you have an inbound column of datatype Char(1) or something character based? And you want to derive a new column from that which will be of type BIT? First I don't think you can have a null BIT value.. but I could be wrong on that, I dunno why one would want a null BIT...
In any event, this message and forum isn't the right place for your question.
Start a new thread, include in the subject something about "SSIS Expression Language".
Your question will get more attention that way.
Post a link to it in this thread. I'll poke around w/ your idea and if I come up w/ something I'll respond to your new post. No promises.
January 11, 2013 at 10:29 am
LTRIM(column1) == "" ? (DT_STR,100,1252)NULL(DT_STR,100,1252) : (column1) == "Y" ? (DT_STR,100,1252)1 : (DT_STR,100,1252)0
I finally arrived at the correct derived column expression.
Let me brief on this.
I am pulling the data from text file and dumping it into sql table.
So when loading column1 value from text file to sql table it should be loaded under BIT datatype.
So that why i have added the above expression to that column.
Hope this would help someone ....
Thanks for your reply. If i come across this kind of issue i will start a new thread.
January 11, 2013 at 10:29 am
LTRIM(column1) == "" ? (DT_STR,100,1252)NULL(DT_STR,100,1252) : (column1) == "Y" ? (DT_STR,100,1252)1 : (DT_STR,100,1252)0
I finally arrived at the correct derived column expression.
Let me brief on this.
I am pulling the data from text file and dumping it into sql table.
So when loading column1 value from text file to sql table it should be loaded under BIT datatype.
So that why i have added the above expression to that column.
Hope this would help someone ....
Thanks for your reply. If i come across this kind of issue i will start a new thread.
March 12, 2013 at 6:21 pm
1) I created an Integration service project
2) I created a common connection Manager for that project.
3) By sharing the same connection manager many number of packages exist under that project.
4) Now I need to set up the package configuration at project level.So that I do not need to go and enable the package config for every single package.
Please suggest.
March 13, 2013 at 7:49 am
I've never tried it, but if your shared connection has the same name in each package, you should be able to create a single Package config in each package of the project using the same Configuration filter for each. Does that make sense?
Or maybe use a single configuration file for all packages in the project. I don't use config files, so maybe that doesn't work.
March 13, 2013 at 7:55 am
Jeff Thanks for your reply. Yeah all my packages use the same connection.But I am not able to create a sql server config because it throws an error saying this package path is not found.
March 13, 2013 at 8:21 am
Please be more detailed on where the error is coming from and what the error says.
What type of configuration are you trying to create? (XML, Table, etc.) and where in the creation process does it complain about not being able to find the package? And what exactly is the error message (every word, please).
March 13, 2013 at 8:28 am
I want to set up SQLTable package config.
1) I established a source(Source_DB) and destination (Destination_DB) connection (OLEDB connection) at connection manager level.
2) So those two connections are shared across all the packages under the solution.
3) Next I opened one of the package to set up table level package config.
4) The package config was saved successfully.
5) But when I clicked on EDIT package config it throws me an error saying 'This package is not laoded successfully.Because the package path is not available in the (Source_DB) and the package path is not available in (Destination_DB). I will send you the exact error details soon.
Thanks
March 13, 2013 at 8:29 am
Is your connection named the same in each package? Table configs are referenced by name in the table.
Try creating a table config in each of your packages that is strictly for this one connection. Don't put any others properties in it.
You could also try using an Environment variable. Not a very versatile package config as you can't change its value too often (ie w/o reboot). But that may be a good option, depending on your needs
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply