June 16, 2011 at 2:34 am
The evaluated query should translate to the sql script below
SELECT * INTO production.dbo.mps_acct FROM Staging.dbo.mps_acct;
both the production and staging dbnames we would like to have as variables.
created those two as variables,
tried various ways, tried the @user::varname, tried the ?
Sincerely,
Sonal.
June 16, 2011 at 2:55 am
You should be able to do this just fine by using an SSIS Expression as the SQL for your Execute SQL task.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 16, 2011 at 4:58 am
Sonal,
Your post isn't very clear. Could you try rephrasing your issue? Include the following details.
1) What type of tasks you are using
2) What you are trying to achieve with the SSIS package
I think you're talking about using an Execute SQL Task, but I need to be sure before I answer. Also, please post what you steps you used in setting up the task and how it failed.
June 16, 2011 at 7:34 am
Brandie,
It is a Execute SQL Task. Trying to move tables from a sql server staging database to a production database
I have created two variables at the container level, not sure if i should create them at the task level.
The error I get is
The query failed to parse. Syntax error, permission violation, or other non specific error
SELECT * INTO ?.dbo.mps_acct FROM ?dbo.mps_acct;
This is the select statement I have in the execute sql task
thanks.
going to try to create the variables at the task level and then try to build an expression
June 16, 2011 at 7:46 am
I suggest that you scope the variables at the package level to ensure that they are persisted between tasks.
Your expression will contain variable names, not ?s.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 16, 2011 at 7:57 am
sonal_anjaria (6/16/2011)
It is a Execute SQL Task. Trying to move tables from a sql server staging database to a production databaseI have created two variables at the container level, not sure if i should create them at the task level.
The error I get is
The query failed to parse. Syntax error, permission violation, or other non specific error
SELECT * INTO ?.dbo.mps_acct FROM ?dbo.mps_acct;
You can't use variables in the Execute SQL Task that way. You'll have to turn the statement into Dynamic SQL to do it.
Try something like (and this is untested)
Declare @MySQL varchar(250), @ProdServer varchar(25), @StageServer varchar(25);
SELECT @ProdServer = ?;
SELECT @StageServer = ?;
SET @MySQL = 'SELECT * INTO ' + @StageServer + ' FROM ' + @ProdServer;
sp_executeSQL @MySQL;
You'll need to double check my syntax. I don't remember if there are parens around the last @MySQL or not.
Do yourself a favor. Make your life easier by putting the entire qualified name into the variable.
Question, though. Why do you need the database names to be variable? What's going on in your environment?
June 16, 2011 at 7:58 am
Phil,
I added the parameters at the container level
then went ahead and added them in the parameter mapping of the execute sql task
The parameter name is User::ProdDB
I set the newparameter name to ProductionDB
I am struggling with the sql statement build
tried various ways to no success
SELECT * INTO @[User::ProdDB].dbo.mps_acct FROM SMARTI01DDB.dbo.mps_acct;
tried
SELECT * INTO ProductionDB.dbo.mps_acct FROM SMARTI01DDB.dbo.mps_acct;
June 16, 2011 at 8:02 am
I have to challenge this entire methodology. A select into is not the fastest way to do this, if you have a source and a destination connection manager that are using expressions to control database name, you can easily write a dataflow that requires no dynamic parameters and runs screaming fast, far faster than SELECT INTO.
CEWII
June 16, 2011 at 8:06 am
Elliott,
I have 190 tables, I don't want to create them all one at a time
Sonal
June 16, 2011 at 8:09 am
Use the input/output wizard and chose the tables and at the end save the package that should give you 98% of the work and be a lot faster than SELECT INTO.
CEWII
June 16, 2011 at 8:13 am
Elliott is correct on this one. Not only is SELECT...INTO going to cause you performance issues, but the use of * will also. Can I ask why you don't just use the Transfer SQL Server Objects task?
And I still don't understand the need for a variable database name.
June 16, 2011 at 8:14 am
Could you elaborate a bit more about the input output wizard, i am quite new at ssis and under a tight deadline
currently searching google on this subject as well
June 16, 2011 at 8:14 am
Elliott Whitlow (6/16/2011)
Use the input/output wizard and chose the tables and at the end save the package that should give you 98% of the work and be a lot faster than SELECT INTO.
Ditto to this.
June 16, 2011 at 8:21 am
sonal_anjaria (6/16/2011)
Could you elaborate a bit more about the input output wizard, i am quite new at ssis and under a tight deadline
In SSMS, open a connection to your Production Server. Right click the production database in question and navigate to All Tasks -> Export. It will come up with the SSIS Import / Export Wizard. Follow the prompts. At one screen you'll get a list of all the tables you can port down. At the end, you'll be asked if you want to run the package immediately. You can save the package whether or not you run it immediately. Give the package a friendly name and put it somewhere where you can find it.
June 16, 2011 at 8:33 am
thank you folks, use the transfer sql server object and done in a second,
much appreciated thank you.
Sonal.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply