Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Flat File Source -> DB table overwrite in T-SQL 2008R2 Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 8:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 43, 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::DestinationTable] + "', 'U') IS NOT NULL
DROP TABLE "+ @[User::DestinationTable] +";

CREATE TABLE "+ @[User::DestinationTable] +"
(
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.
Post #1478608
Posted Monday, July 29, 2013 8:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1478619
Posted Monday, July 29, 2013 8:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 43, Visits: 130
You make a very good point. Definitely something to consider.
Post #1478621
Posted Friday, August 2, 2013 9:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 43, 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::DestinationTable 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::DestinationTable] instead of the table I want it to display.

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

Thank you.


  Post Attachments 
7.jpg (2 views, 368.86 KB)
8.jpg (2 views, 124.55 KB)
9-error code.jpg (1 view, 49.26 KB)
Post #1480456
Posted Friday, August 2, 2013 9:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1480482
Posted Friday, August 2, 2013 10:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 43, Visits: 130
Great, thanks
Post #1480521
Posted Monday, August 5, 2013 7:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 43, Visits: 130
Just wanted to reinstate how much help you were here. You got me off to a really good start Elliot. Thanks again.
Post #1481133
Posted Monday, August 5, 2013 9:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1481142
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse