bluesky204
asked on
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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
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
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.