[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Check Rows Copied After Executing a SSIS Package

Posted on 2008-11-14
Medium Priority
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
  • 3
  • 2
LVL 17

Expert Comment

ID: 22965265
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

ID: 22965581
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

HoggZilla earned 1500 total points
ID: 22966811
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

ID: 23000064
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

ID: 23057761
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

872 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