InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 04:48:03 GMT20http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxi have around 140 packages :(Tue, 19 Mar 2013 10:50:44 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxDo you have a definitive number of packages. If not, then execute each one in series connecting each one via an Success constraint. If one fails it should fail the parent package and the checkpoint will record the last point of execution.Possibly, a foreachloop is not what you should be using.Tue, 19 Mar 2013 02:32:23 GMTDrndrbhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxOne more question:if a package runs successfully i want to move that package from that path automatically or it may be renamed.By doing this i believe this would eliminate the issue which i reported earlier.Because my master package will execute all the packages in the mentioned path and those packages which start with "T."Is there a way that i can do it.Mon, 18 Mar 2013 17:20:29 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxYes I can do that.But my concern is I am using "For Each loop" task for executing several child packages (Which makes million of rows to be loaded into the destination table). If I make the check condition (if the row exist or not) for every single package,it would be time consuming and the package may run for indefinite amount of time.So if there is any other way where I can call all the child packages from a master package and also if I could execute the package from the point of failure then that will solve my need.??Thanks for your reply.Mon, 18 Mar 2013 17:15:43 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxCan you not identify whether the rows previously inserted already exist and then do nothing if they do or insert them if they do not. That way you can just restart the packages and not have to worry about restarting from a certain point or inserting duplicate rows.Check out: http://msdn.microsoft.com/en-us/library/ms140226.aspxread the section 'Defining Restart Points'you will see a comment regarding foreach loopsMon, 18 Mar 2013 17:00:06 GMTDrndrbhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxHi,Today I am facing problem when invoking multiple packages from a master package.Here is the scenario.Master Package has the below tasks conigured.- For Each loop container (To loop through a folder for executing all the packages available in that folder)-Execute Package Task (All the packages are placed in the file system)Child Package :1-consist of 3 Execute SQL task (Which is just a insert statement) -This package runs successfully.Child Package:2-consist of 2 Execute SQL task (Which is just a insert statement)First SQL task runs good.Second SQL task has syntax problem.I have configured Checkpoint in all these Child packages.In my case -Child PAckage:1 runs successfully.But Child Package :2 fails at the second Execute SQL task.So my checkpoint works good when i do the needed modification. But the issue is My For EachLoop container executes Child PAckage:1(Which should not happen) and then the Second Execute SQL Task (Which failed in first run).Because of the checkpoint file SSIS is not running the first Execute SQL task in the Chuld Package:2.Please suggest how to keep track of failed package alone and to restart from that point.ThanksNisha.VMon, 18 Mar 2013 15:24:47 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxOk Got it.Thanks.Fri, 15 Mar 2013 12:58:11 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspx[quote][b]Drndrb (3/15/2013)[/b][hr]If the job is run by SQL agent. Restart SQL agent. The altered env var will then get picked up.[/quote]No need. He's using a SQL table package configuration, not an environment variable.JohnFri, 15 Mar 2013 02:39:33 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxIf the job is run by SQL agent. Restart SQL agent. The altered env var will then get picked up.Fri, 15 Mar 2013 01:16:43 GMTDrndrbhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxThis is because you're trying to configure things that don't exist in the package. If you're sharing one configuration file (or configuration table) between packages, the packages need to have the same structure, at least to the extent that the paths to all the items being configured are the same.JohnWed, 13 Mar 2013 23:16:05 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxBelow is the error message I am getting when I try to set up table valued package configuration.I am referring the same configuration filter across all the packages.Since the SOURCE_DB and Destination_DB connection are not in package level I am not able to create the package configuration. This error message is shown after enabling th epackage config and when clicking on 'EDIT' .TITLE: Microsoft Visual Studio------------------------------Some configurations from table "[dbo].[SSIS_Configurations_New]" with configuration name "Master" could not be loaded due to the following reasons:\Project.Connections[Source_DB].Properties[InitialCatalog]: The configuration refers to an object that does not exist in the package. Package path of the object: '\Project.Connections[Source_DB].Properties[InitialCatalog]'.\Project.Connections[Source_DB].Properties[ConnectionString]: The configuration refers to an object that does not exist in the package. Package path of the object: '\Project.Connections[Source_DB].Properties[ConnectionString]'.\Project.Connections[Destination_DB].Properties[InitialCatalog]: The configuration refers to an object that does not exist in the package. Package path of the object: '\Project.Connections[Destination_DB].Properties[InitialCatalog]'.\Project.Connections[Destination_DB].Properties[ConnectionString]: The configuration refers to an object that does not exist in the package. Package path of the object: '\Project.Connections[Destination_DB].Properties[ConnectionString]'.------------------------------BUTTONS:OK------------------------------Wed, 13 Mar 2013 10:49:49 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxBelow is the error message I am getting when I try to set up table valued package configuration.I am referring the same configuration filter across all the packages.Since the SOURCE_DB and Destination_DB connection are not in package level I am not able to create the package configuration. This error message is shown after enabling th epackage config and when clicking on 'EDIT' .TITLE: Microsoft Visual Studio------------------------------Some configurations from table "[dbo].[SSIS_Configurations_New]" with configuration name "Master" could not be loaded due to the following reasons:\Project.Connections[Source_DB].Properties[InitialCatalog]: The configuration refers to an object that does not exist in the package. Package path of the object: '\Project.Connections[Source_DB].Properties[InitialCatalog]'.\Project.Connections[Source_DB].Properties[ConnectionString]: The configuration refers to an object that does not exist in the package. Package path of the object: '\Project.Connections[Source_DB].Properties[ConnectionString]'.\Project.Connections[Destination_DB].Properties[InitialCatalog]: The configuration refers to an object that does not exist in the package. Package path of the object: '\Project.Connections[Destination_DB].Properties[InitialCatalog]'.\Project.Connections[Destination_DB].Properties[ConnectionString]: The configuration refers to an object that does not exist in the package. Package path of the object: '\Project.Connections[Destination_DB].Properties[ConnectionString]'.------------------------------BUTTONS:OK------------------------------Wed, 13 Mar 2013 10:49:36 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxJeff,Yes the connection is named the same(Configuration filter is reused in each package) across all the packages.I find the error when I click on Edit package configuration.Wed, 13 Mar 2013 08:32:09 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxIs 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 needsWed, 13 Mar 2013 08:29:04 GMTRVA Jeffhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxI 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.ThanksWed, 13 Mar 2013 08:28:38 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxPlease 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).Wed, 13 Mar 2013 08:21:13 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxJeff 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.Wed, 13 Mar 2013 07:55:15 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxI'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.Wed, 13 Mar 2013 07:49:46 GMTRVA Jeffhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspx1) I created an Integration service project2) 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.Tue, 12 Mar 2013 18:21:13 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxLTRIM(column1) == "" ? (DT_STR,100,1252)NULL(DT_STR,100,1252) : (column1) == "Y" ? (DT_STR,100,1252)1 : (DT_STR,100,1252)0I 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.Fri, 11 Jan 2013 10:29:51 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxLTRIM(column1) == "" ? (DT_STR,100,1252)NULL(DT_STR,100,1252) : (column1) == "Y" ? (DT_STR,100,1252)1 : (DT_STR,100,1252)0I 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.Fri, 11 Jan 2013 10:29:29 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxYeah, 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.Fri, 11 Jan 2013 07:13:36 GMTRVA Jeffhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxIf Column1 is bit, it won't be an empty string. It'll either be 1 or 0 or NULL.JohnFri, 11 Jan 2013 01:56:00 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxJeff,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 0Those above expression works well independently.How to write this two condition in a single expression??Please help.ThanksThu, 10 Jan 2013 11:57:02 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxThank 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.Tue, 08 Jan 2013 15:09:35 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxThank you so much for your time and patiece.Will follow your suggestions and let me check.Will update you once I am done.Fri, 04 Jan 2013 12:43:01 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxI 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. Fri, 04 Jan 2013 12:23:53 GMTRVA Jeffhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxThe 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.Fri, 04 Jan 2013 11:59:55 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxMy 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.Fri, 04 Jan 2013 11:43:42 GMTRVA Jeffhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxThanks 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)Fri, 04 Jan 2013 11:28:48 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxWhen 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?Fri, 04 Jan 2013 11:23:08 GMTRVA Jeffhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxHi 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 nameThese 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.-NishaFri, 04 Jan 2013 10:46:39 GMTnishav2http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxI 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 restartedWed, 11 Aug 2010 12:26:14 GMTJeff_aMagnumDBAhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxJeffIf you are changing the values often, I would suggest that you use a SQL Table, and build a simple web page to be able to view or modify the values. XML Config files are good, but they require one to have access to the drive where they are stored. And they are nothing more than a simple text file, that can be deleted or modified incorrectly.I have been using a SQL Table, and have that table in the SQL Maintenance database on each server, in each environment. Very simple and secure to manage, modify and read. Plus it gets backed up every day.I stay away from environment variable like the plague. They do require a service bounce. They get read only when the service starts.Andrew SQLDBAWed, 11 Aug 2010 12:24:37 GMTAndrewSQLDBAhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxOK, here's what I've just tried (Windows XP SP2, SQL Server 2008 SP1 CU4).- In Computer Properties, add a system environment variable called Test, value Test- xp_cmdshell 'set' Result - new variable did not appear in result set- Restart SQL Server- xp_cmdshell 'set' Result - new variable appeared in result set- In Computer Properties, change value of Test variable to Test1- xp_cmdshell 'set' Result - variable still appeared with old value (Test)- Restart SQL Server- xp_cmdshell 'set' Result - variable appeared with new value (Test1)- In Computer Properties, delete Test variable- xp_cmdshell 'set' Result - variable still appears, with value Test1- Restart SQL Server- xp_cmdshell 'set' Result - Test variable no longer appearsLike Brandie, I avoid environment variables and use configuration files where possible.JohnWed, 11 Aug 2010 12:07:56 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxI searched the DSTX file of my package and no where is the value that is coming thru for my problem variable mentioned.Something somewhere on the server I'm running this stuff on is storing the old value.I've logged on and off of the server several times as the agent service account which is the one running my package in the job. Each time checking the value of my problem env var via command prompt > SET command.I have seen posts mentioning "sessions" and bouncing a service would reset a session that may be holding on to an old value, and that would make sense if it was consistent.We'll see if the service restarts work.Tue, 10 Aug 2010 09:18:34 GMTJeff_aMagnumDBAhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxJohn,I can tell you from experience that restarting SQL Server will not help you with this problem. Your environment variables are getting overwritten by another setting. Probably within the package itself. It's an order of processing problem.I wish I could remember the details. You might be able to google the issue though.In my workplace, we often have to change connection settings for our packages. When environment variables didn't work, we went to XML config files. We have one for each of our environments and call the packages using those files. We haven't had a problem since.Tue, 10 Aug 2010 09:07:33 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxThe database engine service or the SSIS service?That still doesn't add up entirely, because I can get changes in env vars to make it thru w/ out any server or server bounces.I'll try both DB engine and SSIS services before I do a box restart.Tue, 10 Aug 2010 09:03:49 GMTJeff_aMagnumDBAhttp://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxI've thought about this a little more and I think what is required is a restart of SQL Server, not the whole computer. This is because the service account picks up the values of the environment variables when it logs in, and to refresh them it needs to log in again. As far as I know, you can't do this without stopping SQL Server and starting it again.JohnTue, 10 Aug 2010 08:59:28 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic966020-148-1.aspxBrandie, thanks for the reply. I view Environment Variables as Package Configurations when talking SSIS. So all Env Vars are package configurations, not all package configurations are environment variables. Other Package Configurations are XML config files, registry entries, Parent package variables, etc, all set up in BIDS under the SSIS>Package Configurations.Environment Variable values are set on a particular machine under System Properties>Advanced Tab>Environment Variables Button>System Variables. I think you can set them using DOS commands and I guess you could edit them directly in the registry as well.I'm calling the package from a SQL 2005 Job, w/ an SSIS step.The package seems to be picking up the old value of the env var. Do to unfortunate circumstances, we have to switch the values of Env Vars a lot and we found that the value we changed from persists when a package is rerun.We aren't able to reboot the server as often as we flip this env var around, in fact its a real big event to bounce a box, even a dev box.And it really seems to be just one particular env var, one we use to store the name of a server. When we flip it to another server name, for some reason the value @ runtime is the old value. I made a bare bones package to simply report the value of this particular env variable and another one I just created brand new. I have two global vars in my package whose default values are just random strings.I mapped my global vars to the env vars and in the package I slap them in to a third var via a script task then email myself that third var via an email task. I deploy my package, run via a job, note the values. I then change the two env vars' values, run the package via the job, note the values. I then redeploy my package, run via the same job, note the values. Results are very weird. From the first run of the package, my one troublesome variable seems to be passing a value not equal to the value of my env variable at the time I first deployed the package. I know this value as it is one of the values we switch among, but I don't recall the last time we had the env var set to this, not in recent past, but probably since the last bounce. For each of my runs of the package, this troublesome variable remains consistent coming across as the same "old" value. The "new" variable seems to work fine, the value I change it to comes to the package correctly only after I redeploy the package.So I guess what we're finding is that the package doesn't retrieve the value of env variables at runtime, and sometimes it doesn't retrieve it at all. It doesn't make sense, but that's what we're working off of. Its either that or env variables have their values stored somewhere that isn't updated when I think they are and a reboot is in fact needed. I talked to one of our Windows Admins and he says that the value returned for a env variable when you type "SET" in to the DOS prompt is expected to be that actual/real value of the env variable. We're just not seeing that to be true.My next step is to request a bounce of the server and see how that impacts things. But from what I'm reading is that a bounce is not necessary.Tue, 10 Aug 2010 08:53:46 GMTJeff_aMagnumDBA