November 5, 2009 at 2:19 pm
How do i see a contents of a package in SSIS without running it...
I want to know which table or if there a procedure that it is using to transfer the data from one sql server 2005 machine to another??
Any help will be appreciated..
Thanks
Karen
November 5, 2009 at 2:28 pm
Open the package in Business Intelligence Development Studio. Should be in the same directory on the Start menu as Management Studio is.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2009 at 2:50 pm
Gsquared...
I exported the package from SSIS to the file system and saved the package in my "My documents" folder...
and then opened that file is BIDS.. but i dont see what kinda tables or procedures that this package is using...
all i see a diagram with Nontransactionalsql, start transaction etc... and if i right click on them and hit edit i still dont see if it is a particular procedure or which tables are used...
Can you please guide me and where can i look for that informtion in BIDS... I am new to SSIS 2005 I have used DTS 2000 but as I see there has been huge upgrade to a package in Sql Server2005
Any help will be appreciated...
Thanks,
KAren
November 5, 2009 at 11:41 pm
Open the package from within BIDS.
It is safest to try to do it when opening the .sln file instead of the .dts file that was exported from SQL Server. Do you have the original source code?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 6, 2009 at 6:33 am
no these files were created by someone... so we dont have access to the source code or the .sln file... Is there any ways i can find out whats in the dts package?
November 6, 2009 at 9:07 am
If you don't have the sln, create a new project. Once the project is created and open, right click packages from solution explorer, select add existing package. This should add the package for you so can browse the package contents.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 6, 2009 at 9:47 am
Thanks for the answer...
so when i give add existing package should it be from the file system or can i connect directly to the integration services and grab that package and if i go through the second route will I be able to see all the underlying tables and procedure and everything it uses...
Thanks,
Karen
November 6, 2009 at 9:48 am
I typically do it from the file system and haven't tried to do that directly from the msdb store.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 6, 2009 at 11:29 am
When I open My .dtsx file from the file system in Bids.. why dont I see the data flow task...
Am I missing something when i am downloading it?
Any help will be appreciated.
Thanks,
karen
November 6, 2009 at 11:56 am
When you opened the package (added the package in the Solution) did you get any errors about it? and one more question do you see other components of the control flow? and what about connection managers. etc.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 6, 2009 at 12:26 pm
Bru Medishetty (11/6/2009)
When you opened the package (added the package in the Solution) did you get any errors about it? and one more question do you see other components of the control flow? and what about connection managers. etc.
I didnt get any errors when i add the package to it..
In my control flow i can see components like
NonTransactionalSql,
Start Transcation,
Transcation scoping sequence
AllowedToFailPrologueSQL
PrologueSQL
Foreach Loop Container
Transfer Task
Execute Inner Package
EpilogueSql
Commit Transaction
PostTransaction Sql
RollBack Transaction
CompensatingSql
I also see the connection managers
If i click on the source and destination connection I can see the databases..
but if i click on the others like AllowedToFailPrologueSql(right click and edit) the usage type is Existing file and it points to a file in a temp directory which doesnt exists on the system it came from either..
C:\Documents and Settings\Administrator\Local Settings\Temp\2\tmp22.tmp
and i have a warning in the window that says "File specified does not exist"
Thanks,
KAren
November 6, 2009 at 12:39 pm
It looks like the SSIS Package was create without using the DataFlow task, the entire Data transfer must have been implemented in the Execute SQL tasks only, for which you need to see the Execute SQL Tasks scripts.
From the List of the components (they are Names) that you have mentioned, I can make out the Control Flow type for some of them but not all, for example EpilogueSql or Commit Transaction. I guess some of them are Execute SQL Tasks. You need to go through each one of them to find more info what each one is doing.
Also about the error mentioned, have you checked the Original location? (server where it currently exists) do you see any such files in the same location.
By the way what type of task is AllowedToFailPrologueSql
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 6, 2009 at 12:55 pm
Bru Medishetty (11/6/2009)
It looks like the SSIS Package was create without using the DataFlow task, the entire Data transfer must have been implemented in the Execute SQL tasks only, for which you need to see the Execute SQL Tasks scripts.From the List of the components (they are Names) that you have mentioned, I can make out the Control Flow type for some of them but not all, for example EpilogueSql or Commit Transaction. I guess some of them are Execute SQL Tasks. You need to go through each one of them to find more info what each one is doing.
Also about the error mentioned, have you checked the Original location? (server where it currently exists) do you see any such files in the same location.
By the way what type of task is AllowedToFailPrologueSql
The AllowedToFailPrologueSql is a execute sql task.. and your correct most of the task in here are exceutable task and if i take a look at the properties the IsStoredProcedure property is set to false..
How can i find the execute Sql Tasks scripts?
Is there a place where i can find them??
November 6, 2009 at 1:11 pm
if i take a look at the properties the IsStoredProcedure property is set to false..
How can i find the execute Sql Tasks scripts?
Is there a place where i can find them??
Please find the Picture in the attachment, What you need to look for is the SQL Source Type and SQL Statement, there would be an ellipsis button click on that would open the entire script for that task.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 6, 2009 at 1:19 pm
The sqlSourceType is a fileConnection and the FileConnection is AllowedtoFailPrologueSql
and i cannot find that file from the server where the ssis package is located...
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply