x

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

more ▼

asked Oct 21, 2013 at 08:57 AM in Default

avatar image

ruancra
1.2k 31 37 45

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?

Oct 21, 2013 at 11:10 AM Shawn_Melton

As well, if you can do it in a T-SQL script you can do it in SSIS.

Oct 21, 2013 at 11:11 AM Shawn_Melton

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.

Oct 21, 2013 at 11:50 AM ruancra

It's not letting me attach the query

Oct 21, 2013 at 11:54 AM ruancra

It seems like I've heard that .sql is a restricted extension for the site. Try changing the extension to .txt

Oct 21, 2013 at 01:56 PM KenJ
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Oct 22, 2013 at 07:47 AM

avatar image

Håkan Winther
16.6k 37 46 58

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

Oct 22, 2013 at 08:17 AM ruancra

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.

Oct 22, 2013 at 08:26 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Oct 22, 2013 at 07:18 AM

avatar image

ruancra
1.2k 31 37 45

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x1221
x71

asked: Oct 21, 2013 at 08:57 AM

Seen: 957 times

Last Updated: Oct 22, 2013 at 08:26 AM

Copyright 2017 Redgate Software. Privacy Policy