Check Rows Copied After Executing a SSIS Package

Posted on 2008-11-14
Last Modified: 2013-11-10
Hey Guys,

I have just currently switched over from DTS to SSIS.  This particular package that I work on has a data flow task that exports a SQL table to MS Access.  However,  the export takes a long time compared to DTS and I don't know how many rows are copied since the SSIS interface no longer gives me the status of rows copied at a given time.

In DTS, I like how SQL gives you status and rows copied in brackets such as "running (2000)", so when it is stuck I know how to react.  Does SSIS have this kind of interface during execution?  The execution progress doesn't give me anything except for the message "phase is starting"

Thanks for your help!
Question by:bluesky204
    LVL 17

    Expert Comment

    If you run the package from your desktop, Execute Package, you can go to the Data Flow task and see the same type status as DTS provided - the running count.
    How did you create this new SSIS package? Please don't tell me you used the Migration Wizard. LOL. Just kidding. I have had no luck with it, I was 0 for 20 before I just gave up and starting creating the packages manually and using the built in SSIS wizards for package creation - not migration.
    Is the server the same as what you ran DTS/2000 on? Where is the Access db in relation to the SQL Server?
    There is no reason why it should take longer in SSIS to export the data than it did in DTS, something else is not right. I can help. Start by answering my questions here. Thanks.

    Author Comment

    hey Hoggzilla, yea I used Migration Wizard, the trick is if there's trailing space for your DTS, it wouldn't work, I had to remove the empty space and everything worked fine.

    The SQL server is on a different server, I already specified logins and connected successfully as I was able to run some queries before exporting it.   Access database is sitting on a mapped shared folder.  I also checked the table in Access and it was getting records from SSIS, but very very slowly :(

    So you can only see the row count in BIDS?  Data viewer in Data Flows tab is pretty good but I guess I have to stay in Debug mode?
    LVL 17

    Accepted Solution

    Yeah, you can only see the row count progression there. But even with DTS we could not see the row count if it was a scheduled job in SSAgent, it had to be on our desktop. I do miss some of the DTS features though. It has been a huge curve moving to SSIS.
    In the new SSIS package, what are your components related to this task. You have a Data Flow Task? In the Data flow tab you have an OLEDB Source and what type of Destination object for the Access Database? Post a screen shot or word doc with pictures please, that is very helpful since I can't see it.

    Author Comment

    yea I agree, I really miss DTS considering the fact that all my training is done in that environment =P

    In my SSIS package, I have 2 data flow tasks, basically first task imports the raw excel table into SQL, then the package runs through some update queries to massage then goes to the 2nd data flow tasks, which is exporting the final table to the a table in Access database.

    Thanks for your help Hogg, but I cannot put up too many picture as some of the data is somewhat confidential.  Thanks tho, now I know SSIS doesn't have that DTS execution interface, I guess the the new designed SSIS is made to be tested within BIDS and become an scheduled automation after, that's probably why the progress information during actual execution is not that helpful.

    thanks, I appreciated.

    Author Comment

    Hey Hoggzilla,
    As you can see from my attached picture, I have a OLD DB Source, which is a SQL table, then it goes through data conversion then enters OLE DB Destination, which is a Access database on shared folder

    Connection1 is the SQL server connection, provider is Native OLE DB\MS OLE DB Provider for SQL Server
    Connection 2 is the Access connection, Provider is Jet 4.0 OLE DB please refer to 2nd screenshot

    The weird thing is my manager can execute my exact same package in less than 2 min, while if I run it on my notebook, it took more than 10 mins and still wouldn't finish, it would just get stuck in "Execution phase is beginning", any ideas?

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now