December 21, 2012 at 10:51 am
Hi all!!!
I inherited an interface process (Oracle text file to SQL Server table) which uses a SSIS 2005 package developed in Visual Studio 2005. I'm new to SSIS, and need to do a change in the file to table mapping, since more columns will be added to the Oracle file. I found there are several DTSX files with the same name as the package being executed, but haven't found which one is the actual package being executed.
Is there a way to know from within SSIS what is the package file name and where it is located?
Thank you,
December 26, 2012 at 2:21 pm
EL Jerry (12/21/2012)
Hi all!!!I inherited an interface process (Oracle text file to SQL Server table) which uses a SSIS 2005 package developed in Visual Studio 2005. I'm new to SSIS, and need to do a change in the file to table mapping, since more columns will be added to the Oracle file. I found there are several DTSX files with the same name as the package being executed, but haven't found which one is the actual package being executed.
Is there a way to know from within SSIS what is the package file name and where it is located?
Thank you,
Is this being run as a SQL Server job or is it being run from Visual Studio?
If it is a scheduled SQL Server job, the best way to make sure you're working on the latest package is to open Visual Studio, go to the project in which you'd like to have the package and right click on the SSIS Packages node.
From there, click on Add Existing Package. Select the server where your job is and then select the package. This will create a copy of the current SSIS package. You can alter it to your heart's delight, but no changes will be made to the running job until you save a copy back to the server.
If you need more detail, just let me know.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 26, 2012 at 2:48 pm
If being run as a SQL Agent Job, you should be able to track down if it is in MSDB or being run from the file system.
Then you can either Export from MSDB (see Books Online), or take a copy from the file system.
Whatever you end up doing, consider some documentation and source control.
As you can see, it is not much fun to inherit and have to pick up the pieces.
Especially if you guess wrong. :crazy:
Not sure, but if you are using a different account, if the previous scenario would work.
It sounds like you are just looking through the file system and seeing several interations.
December 26, 2012 at 2:50 pm
Thank you for your reply, Stefan.
Stefan Krzywicki (12/26/2012)
Is this being run as a SQL Server job or is it being run from Visual Studio?
The package is being executed in a step of a SQL Server job.
If it is a scheduled SQL Server job, the best way to make sure you're working on the latest package is to open Visual Studio, go to the project in which you'd like to have the package and right click on the SSIS Packages node.
From there, click on Add Existing Package. Select the server where your job is and then select the package. This will create a copy of the current SSIS package. You can alter it to your heart's delight, but no changes will be made to the running job until you save a copy back to the server.
What makes me get confused is there are several projects in Visual Studio (from IntegrationServicesProject1 to IntegrationServicesProject9), all of them having the same subfolder structure, all of them containing the file named "Import Oracle File.dtsx". I would think the file with the latest timestamp would be the one currently deployed, but i'm not sure.
If you need more detail, just let me know.
OK, let's suppose I did the changes to the package. I would have to re-deploy the package back to the server, right?
The package just contains a data transformation from a flat file to a database table, but the new flat file will have more columns than the current flat file. If the new columns are not mapped, they won't be loaded, am I correct?
Saludos,
December 26, 2012 at 2:57 pm
EL Jerry (12/26/2012)
Thank you for your reply, Stefan.Stefan Krzywicki (12/26/2012)
Is this being run as a SQL Server job or is it being run from Visual Studio?The package is being executed in a step of a SQL Server job.
If it is a scheduled SQL Server job, the best way to make sure you're working on the latest package is to open Visual Studio, go to the project in which you'd like to have the package and right click on the SSIS Packages node.
From there, click on Add Existing Package. Select the server where your job is and then select the package. This will create a copy of the current SSIS package. You can alter it to your heart's delight, but no changes will be made to the running job until you save a copy back to the server.
What makes me get confused is there are several projects in Visual Studio (from IntegrationServicesProject1 to IntegrationServicesProject9), all of them having the same subfolder structure, all of them containing the file named "Import Oracle File.dtsx". I would think the file with the latest timestamp would be the one currently deployed, but i'm not sure.
This is why I suggest you create a new IS project, name it something that shows it is what you've created and add the package directly from the server. That'll be the package being used and you'll be better able to keep track from there.
If you need more detail, just let me know.
OK, let's suppose I did the changes to the package. I would have to re-deploy the package back to the server, right?
Right. Save Copy As. You can then either save it with the same name, overwriting the package on SQL Server and then you don't have to change the job, but you lose the old version or you save it as a new package name and change the job step to use the new package.
The package just contains a data transformation from a flat file to a database table, but the new flat file will have more columns than the current flat file. If the new columns are not mapped, they won't be loaded, am I correct?
You are correct. You should modify the package once you've loaded it into Visual Studio to map the new columns.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 26, 2012 at 3:03 pm
Thank you for your response, Greg.
Greg Edwards-268690 (12/26/2012)
If being run as a SQL Agent Job, you should be able to track down if it is in MSDB or being run from the file system.Then you can either Export from MSDB (see Books Online), or take a copy from the file system.
I can see it in SSIS\Stored Packages\MSDB. Can I just export it and work with in in VS2005?
Whatever you end up doing, consider some documentation and source control.
As you can see, it is not much fun to inherit and have to pick up the pieces.
Especially if you guess wrong. :crazy:
Definitely!! 😀
December 27, 2012 at 4:14 am
EL Jerry (12/26/2012)
Thank you for your response, Greg.Greg Edwards-268690 (12/26/2012)
If being run as a SQL Agent Job, you should be able to track down if it is in MSDB or being run from the file system.Then you can either Export from MSDB (see Books Online), or take a copy from the file system.
I can see it in SSIS\Stored Packages\MSDB. Can I just export it and work with in in VS2005?
So long as SSIS 2005 is what the server is using, yes. If the server is using 2008, though, you will probably need to work with the package in VS2008. You'll know if you get errors when trying to open the package in 2005.
BTW, make sure to download any XML config files locally if the package uses them. That way you can point the package to a dev server while you're making your changes.
December 27, 2012 at 2:45 pm
Thanks a lot for your reply, Brandie.
Brandie Tarvin (12/27/2012)
EL Jerry (12/26/2012)
Thank you for your response, Greg.Greg Edwards-268690 (12/26/2012)
If being run as a SQL Agent Job, you should be able to track down if it is in MSDB or being run from the file system.Then you can either Export from MSDB (see Books Online), or take a copy from the file system.
I can see it in SSIS\Stored Packages\MSDB. Can I just export it and work with in in VS2005?
So long as SSIS 2005 is what the server is using, yes. If the server is using 2008, though, you will probably need to work with the package in VS2008. You'll know if you get errors when trying to open the package in 2005.
BTW, make sure to download any XML config files locally if the package uses them. That way you can point the package to a dev server while you're making your changes.
This is still an all-2005 environment, so there will be no problem.
January 8, 2013 at 9:16 am
Hello, People!!!
Just to let you know, because of a deadline constraint I had to translate the functionality of the SSIS package to T-SQL, which was faster than trying to learn/understand/re-code using VS2005. Fortunately I met the deadline and the job is perfectly working with the new file format.
Thank you again for your help, I realized my knowledge of VS is not as broad as I had thought, so I will try to get some learning material in a near future.
Saludos,
January 8, 2013 at 9:33 am
Glad you got it worked out.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply