How do i see the contents of a package in SSIS

  • 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

  • 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

  • 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

  • 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

  • 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?

  • 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

  • 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

  • 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

  • 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

  • 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.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • 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

  • 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


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • 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??

  • 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.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • 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