question

ruancra avatar image
ruancra asked

Import data using SSIS

Hi all We have a front-end server with Management Studio & SSIS installed. I use this server to connect to 3 SQL servers (Live, Live_Replicated and Reporting). The Live_Replicated and Reporting servers are on different subnets, but the front-end server can access them both. Everyday after data from Live is replicated to Live_Replicated server, i run a SSIS package to sync tables between Live_Replicated and the Reporting server. The problem i have is the Transactions table have about 400 000 new transactions daily and takes too long to import via the SSIS package. We generate a INSERT script,save to a .sql file straight on the Live_Replicated server, then open the file on the Reporting server to insert the transactions, and insert in batches of 50 000. Below is a example of the script: EXEC usp_INS_Transactions 246248974,'2013-10-18 12:29:28.963',72959,4464,5,19,20.00,22,73505,6,16 EXEC usp_INS_Transactions 246248975,'2013-10-18 12:29:29.087',69135,4464,5,28.5,30.00,22,69680,3,16 Is there any way this can be added to the SSIS package and not manually have to be inserted to the Reporting database? Any ideas will be appreciated. Thanks
sql-server-2008ssisimport-data
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So you are doing the SQL file execution inplace of the SSIS package because it takes to long to load? Can you provide details of the setup or steps in the SSIS package?
0 Likes 0 ·
As well, if you can do it in a T-SQL script you can do it in SSIS.
0 Likes 0 ·
The SSIS package still gets run every morning to sync the smaller tables, all it basically does is a lookup between the Replicated and Reporting server. The transaction table is the only table not part of the package as a result of the size. We run the query below in Management Studio on the replicated server, save the results to a .sql file. Then open that same .sql file on the Reporting server to insert the transactions, we insert 50 000 rows at a time.
0 Likes 0 ·
It's not letting me attach the query
0 Likes 0 ·
It seems like I've heard that .sql is a restricted extension for the site. Try changing the extension to .txt
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
400 000 records is no problem for SSIS. I would recommend you to use the BULK Insert task in SSIS and configure a batch size of 50 000. If you really want to increase the speed in SSIS, you can use a script Component as a "producer" and use multiple script Components to as "consumers" that run in parallell. If you want to know more about the Producer/consumer technique you could tweet @sqlrunner, he held an excellent presentation about this at Pass Summit 2013.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Hakan Thanks for your comment, as mentioned the 2 servers are on different subnets, hence why we have to generate the INSERT file (.sql) on the replicated server and then open and run it on the Reporting server. Is it possible to save query results to a .sql file in SSIS? If so, then i can have a look at the BULK insert task. Thx
0 Likes 0 ·
Hi! You can use SSIS to create a CSV file by using a dataflow task with flatfile destination. You can even use FTP task to send the file to any server with FTP services running.
0 Likes 0 ·
ruancra avatar image
ruancra answered
[link text][1] [1]: /storage/temp/ 1123-generate_transactions.txt

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.