SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Flat File Source -> DB table overwrite in T-SQL 2008R2


Flat File Source -> DB table overwrite in T-SQL 2008R2

Author
Message
Ryan1
Ryan1
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 130
Thank you for your comment Jeff, I will definitely be trying that next.

For anyone else who is attempting this, here is how I solved what I was doing wrong. I entered the following code into the first Execute SQL task (" " is included in the actual code):

"IF OBJECT_ID('" + @[User:BigGrinestinationTable] + "', 'U') IS NOT NULL
DROP TABLE "+ @[User:BigGrinestinationTable] +";

CREATE TABLE "+ @[User:BigGrinestinationTable] +"
(
Field1 INT NOT NULL,
Field2 INT NOT NULL );
GO
"

This got it to check if the variable I had set was a table, to drop it if it was, and recreate a new table with two columns: Field1, and Field2.

Does anyone know if there is a proper way to copy all of the column headers before dropping the table by means of SQL code? This way I could create a new table with all of the same fields automatically. I know I could right click in 'Microsoft SQL Management Studio', but I am trying to optimize this.

-Also note I am using a flat file source which does not have the correct column headers, the specific column headers exist only in the tables I am trying to overwrite.

Thank you.
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10086 Visits: 5314
You could do a SELECT * INTO dbo.NEwTableName FROM dbo.OldTableName WHERE 1=0

BUT I don't recommend it. If that table gets modified for any reason you will now carry that modification forward. I MUCH prefer the package to know the table structure and create the table that matches the data flow inside that package. You KNOW the structure of the table at EVERY run and the structure that was tested with the package. If you are loading into a persistent table that is shared with other loads this does not apply but tables I use for a single load I prefer to be managed within the package..

CEWII
Ryan1
Ryan1
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 130
You make a very good point. Definitely something to consider.
Ryan1
Ryan1
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 130
I'm not quite sure what happened, I have seemingly back slid since this was successfully working.

I am now getting a failure to lock variable. It doesn't seem like IsSourPathVariable would apply to this and checkpoints are disabled. The User:BigGrinestinationTable string does work when I use it as the variable which defines my OLEDB Destination, so this is likely my syntax for TRUNCAT TABLE with a variable? I have the syntax setup exactly as you had before.

-It doesn't seem to 'evaluate' properly in expression manager. It continuously returns as @[User:BigGrinestinationTable] instead of the table I want it to display.

I have attached screenshots that show various settings which may apply.

Thank you.
Attachments
7.jpg (4 views, 368.00 KB)
8.jpg (5 views, 124.00 KB)
9-error code.jpg (3 views, 49.00 KB)
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10086 Visits: 5314
The error tells me you have something set to expect a variable name BUT you are passing it a command.

I actually see several problems.

The Variable User::TruncateTable is incorrectly formed. Use the expressions of the variable to set it to that truncate statement, the value of the variable should be the actual statement that it calculates NOT the formula to get there.

The next problem is you are using BOTH variables and expressions on the execute task, take out the expression OR change the type to something else and let the expression feed it.

CEWII
Ryan1
Ryan1
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 130
Great, thanks
Ryan1
Ryan1
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 130
Just wanted to reinstate how much help you were here. You got me off to a really good start Elliot. Thanks again.
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10086 Visits: 5314
You are very welcome, glad I could help. SSIS has a fairly steep learning curve. Some of the other topics are even steeper.. Good luck..

CEWII
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search