Passing Varibale in Execute SQL Task for SSIS

Question

Post reply

Passing Varibale in Execute SQL Task for SSIS

Jaimin Patel

Old Hand

Points: 333
More actions
June 16, 2011 at 2:38 pm

#389754

Guys,
Help me with Exec SQL Task in SSIS.
I have pakage varibale called "Source_Server" and I want to pass that value to my SQL script. Basically, the script is simple that dumps data from one server to another server. I just want to have "Server" being replaced by Source_Server Variable in following SQL.
Insert into database.dbo.table1 Select * from Server.database.dbo.table1
Insert into database.dbo.table2 Select * from Server.database.dbo.table2
Insert into database.dbo.table3 Select * from Server.database.dbo.table3
Insert into database.dbo.table4 Select * from Server.database.dbo.table4
Insert into database.dbo.table5 Select * from Server.database.dbo.table5
Insert into database.dbo.table6 Select * from Server.database.dbo.table6
Thanks,
Jaimin

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 1

Found this.

http://technet.microsoft.com/en-us/library/ms140355.aspx

and this, with pictures.

http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx

I've also used a script task to build the SQL and you can access the variables there too.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

I would do this in SSIS it is very simple. I would absolutely NOT do it over linked servers, you will see orders of magnitude performance increases in SSIS.

Is the table count always the same?

CEWII

Jaimin Patel Old Hand Points: 333 More actions · Answer 3

Yest the table counts will be same. Simple dump from one server to another server

Jaimin Patel Old Hand Points: 333 More actions · Answer 4

Chuck,

I have seen those articles already before posting here. It won't help me since it's not addressing what i am looking for.

Thanks,

Jay

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Jaimin Patel (6/17/2011)
Yest the table counts will be same. Simple dump from one server to another server

Are the tables truncated first?

CEWII

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Ok, here is some pictures of a package to do this that I threw together in about 5 minutes, adding the guts would have taken about 5-10 minutes more..

CEWII

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 7

Since you originally said that "I have pakage varibale called "Source_Server" and I want to pass that value to my SQL script.", I posted links to articles about how to do that. Thanks.

Jaimin Patel Old Hand Points: 333 More actions · Answer 8

Elliott, is there any other alternate route than what you told. I thought of doing those table transfers too but it just i have over 100 tables and it will defi be lot of work creating manual data flow.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

You could use the import/export wizard to have it do most of the work, right there near the end it gives you the option to save the package. You can use that to do the grunt work, the prettyfy it and use it.

CEWII