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

How to know which DTSX file was deployed in a server. Expand / Collapse
Author
Message
Posted Friday, December 21, 2012 10:51 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 10, 2014 6:02 PM
Points: 3,764, Visits: 1,051
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,


"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan
Post #1399528
Posted Wednesday, December 26, 2012 2:21 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:10 AM
Points: 2,694, Visits: 6,895
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
Post #1400377
Posted Wednesday, December 26, 2012 2:48 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 1,164, Visits: 6,274
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.

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.
Post #1400385
Posted Wednesday, December 26, 2012 2:50 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 10, 2014 6:02 PM
Points: 3,764, Visits: 1,051
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,


"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan
Post #1400386
Posted Wednesday, December 26, 2012 2:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:10 AM
Points: 2,694, Visits: 6,895
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
Post #1400387
Posted Wednesday, December 26, 2012 3:03 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 10, 2014 6:02 PM
Points: 3,764, Visits: 1,051
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.

Definitely!!


"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan
Post #1400390
Posted Thursday, December 27, 2012 4:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 7,074, Visits: 6,235
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1400552
Posted Thursday, December 27, 2012 2:45 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 10, 2014 6:02 PM
Points: 3,764, Visits: 1,051
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.


"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan
Post #1400746
Posted Tuesday, January 08, 2013 9:16 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 10, 2014 6:02 PM
Points: 3,764, Visits: 1,051
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,


"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan
Post #1404319
Posted Tuesday, January 08, 2013 9:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 7,074, Visits: 6,235
Glad you got it worked out.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1404341
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse