Check Rows Copied After Executing a SSIS Package

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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
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.
bluesky204Author Commented:
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?
Steve HoggITCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bluesky204Author Commented:
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.
bluesky204Author Commented:
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.