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 Thursday, July 25, 2013 1:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
You are most of the way there. I recommend though that the destination DB NOT be included in the command, set the destination database in your destination connection.

I have uploaded a package that illustrates how this can be done.
https://dl.dropboxusercontent.com/u/85082194/SSC/Package-1477191-364-1.dtsx

I have also attached it here in this post..



First I have setup these variables:

I am using expressions to build two commands, one to build the work table and the other to truncate it. The are fed from the variable DestinationTable.



They are then used in the Exec SQL Tasks as such:




We then go to the Data Flow Task, you will notice that I am using an OLEDB Source, this is just to make the demo easier.


The Source just gives 5 rows of data:


The Destination is a setup a little different:

You'll notice the Data Access Mode and the variable. Because the Destination table has an Identity for Field1 the Keep Identity is checked.

This gets us most of the way, you probably noticed the DestinationDatabase variable, that is used to set the catalog at the destination. There are several ways to do this, you can set the whole connection string at run time or a couple other ways. I'm keeping it really simple.

You'll notice the little formula symbol next to the Connection name, I have configured an expression to set the catalog (database) name.



I hope this sheds some light on the situation for you.

CEWII


  Post Attachments 
Package-1477191-364-1.zip (2 views, 4.74 KB)
Post #1477716
Posted Friday, July 26, 2013 7:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:36 PM
Points: 43, Visits: 128
Thank you for your detailed response, you are a very big help as there is not a lot of useful/detailed information on this subject matter.

-I have the property expression set to "ForcedExecutionValue", Here is the code:

"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N' "+ @[User::DestinationTable] +" ') AND type in (N'U'))
DROP TABLE "+ @[User::DestinationTable] +" (
[Field1][int][ IDENTITY(1,1) NOT NULL,
[Field2][int] NOT NULL )
GO
"

Here is the error message text copied from the progress tab:

[Execute SQL Task] Error: Executing the query ""IF EXISTS (SELECT * FROM sys.objects WHERE object..." failed with the following error: "Incorrect syntax near '+'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

-My ResultSet is 'None' as yours is.

Could this possibly be my parameter mapping? Attached is what I am currently working with, I have also tried it with no parameter mapping at all.

Thank you.


  Post Attachments 
Parameter Mapping.jpg (1 view, 70.03 KB)
Post #1477986
Posted Friday, July 26, 2013 10:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
There is no mapping needed.

I'm having a little trouble following you. Where did you set the expression? What object? What property? What was your exact formula?

CEWII
Post #1478111
Posted Friday, July 26, 2013 11:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:36 PM
Points: 43, Visits: 128
I set each expression to it's appropriate Execute SQL Task, I assigned no other expressions.

Both of the properties are set to ForceExecutionValue. It didn't co-align with MSDNs definitions per
http://msdn.microsoft.com/en-us/library/ms137749.aspx
-but it was one of the few that actually allowed me to debug still.


Attachment 1 displays the way everything is setup
Attachment 2 displays the first Execution SQL Task(build table)
Attachment 3 displays the second Execution SQL Task (truncate)

I understand that Field1, and Field2 wont be written into my actual table, but I need to create some type of relation when creating a table so I figured I would just keep it.

-This is failing on the build table.

Thanks


  Post Attachments 
1.jpg (2 views, 110.95 KB)
2.jpg (2 views, 186.84 KB)
3.jpg (2 views, 135.65 KB)
Post #1478135
Posted Friday, July 26, 2013 12:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
I understand your mistake now. You can't use that property, it doesn't line up to the command to execute. SQLStatementSource is the property you need to set. With the type being direct in.

Try that.

CEWII
Post #1478142
Posted Friday, July 26, 2013 1:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:36 PM
Points: 43, Visits: 128
Nice, we are making headway.


I am now receiving this error when debugging:

[Execute SQL Task] Error: Executing the query "IF EXISTS (SELECT * FROM sys.objects WHERE object_..." failed with the following error: "Incorrect syntax near 'Field1'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

-I don't have the variable stated anywhere else
-Tried deleting the middle Execute SQL Task
-Checkpoints are disabled
-Attachment 5 is the Execute SQL Task Properties
-Attachment 6 is the Package properties

Thanks.


  Post Attachments 
5.jpg (1 view, 120.64 KB)
6.jpg (1 view, 248.11 KB)
Post #1478169
Posted Friday, July 26, 2013 1:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
You don't need to define or map the variable in the component, the reference to it in the expression handles all that. You need to find out what it is building as the output of the expression. go into the express designer and click the evaluate and see what it builds, make sure it is building a valid statement. I can't tell you much more. I have provided all the basics to get you there, now you have to make sure what it creates will actually run.

CEWII
Post #1478174
Posted Friday, July 26, 2013 2:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:36 PM
Points: 43, Visits: 128
It definitely builds, I must have the wrong code somewhere though. Thank you for your help, updates will continue.
Post #1478192
Posted Friday, July 26, 2013 2:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
In the message with all my screen prints look at the very last one, You need to see what is in Evaluated Value, THAT is what is going to be run.

CEWII
Post #1478196
Posted Friday, July 26, 2013 9:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
On the other hand, you could use xp_DirTree to find the file names and use BULK INSERT to load the file into a table using only T-SQL.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1478264
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse