• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

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!
SSISExecution.JPG
0
bluesky204
Asked:
bluesky204
  • 3
  • 2
1 Solution
 
HoggZillaCommented:
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.
0
 
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?
0
 
HoggZillaCommented:
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.
0
 
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.
0
 
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?
SSISSLow.bmp
SSISSlow2.JPG
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now