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 Wednesday, July 24, 2013 11:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 9:29 AM
Points: 40, Visits: 122
Hello SQLSC,

I am extremely new to SQL. As you can tell is my first post on the forum, I have a feeling we are going to get to know each other very well

I have been tasked to use a flat file source -> overwrite a table on a 2008R2 SQL database with the data in the flat file source. I am able to utilize BIDS to create a new table in which my data can upload to no problem. I cannot however overwrite the table that it needs to go. It seems to run in bids no problem with no errors. Any detailed help would be much appreciated.

Thank you and have a nice day.
Post #1477191
Posted Wednesday, July 24, 2013 11:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Not entirely clear what the issue is..

In your process are you needing to drop the table then load it? Or do you just need to clear the table? Please go into additional detail.

CEWII
Post #1477192
Posted Wednesday, July 24, 2013 11:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 9:29 AM
Points: 40, Visits: 122
The flat file data needs to replace the data in the columns of the database table.
The table in the database has column names that the flat file does not as well.
Post #1477193
Posted Wednesday, July 24, 2013 12:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Ok, so I read that as you need to empty the destination table.

On the Control Flow of the SSIS package add a Execute SQL Task, chose the correct connection, and put in a TRUNCATE TABLE command for the table you need to empty, connect the Exec SQL task to the Data-Flow task and you should be in good shape.

CEWII
Post #1477196
Posted Wednesday, July 24, 2013 12:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 9:29 AM
Points: 40, Visits: 122
Thank you so much Elliot!!
Post #1477198
Posted Wednesday, July 24, 2013 12:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 9:29 AM
Points: 40, Visits: 122
Is there any way to fully map each Available Input Column to each Available Output Column in the OLE DB Destination Editor without dragging each column name across, making a link?
Post #1477199
Posted Wednesday, July 24, 2013 12:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
You are very welcome, this is a VERY common design pattern, clean the receiver, fill the receiver, then run some process to do something with the data..

CEWII
Post #1477201
Posted Wednesday, July 24, 2013 12:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
If they have the same names they will usually automap, but if they don't then no, you have to handle the mapping.

CEWII
Post #1477203
Posted Wednesday, July 24, 2013 12:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 9:29 AM
Points: 40, Visits: 122
Awesome, thank you so much.
Post #1477207
Posted Thursday, July 25, 2013 12:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 9:29 AM
Points: 40, Visits: 122
Are there ways for me to replace the flat source file name and database table name with variables?

-I started with the Execute SQL task in control flow, trying to use a variable for a table name in that area first.
-Trying to piece together information on how to accomplish this from different websites, but I have hit a stand still.
*listed attachments display print screens of my settings.


1.
The 'value' of my 'execute' variable is:

TRUNCATE TABLE [nevada].[dbo]@tableName;
----------
2.
My ResultSetType is set to: ResultSetType_None
-----------
3.
Resulting Error when debugging:
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [nevada].[dbo]@tableName;" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

-Parameter Name is 0
-ResultSet is greyed out in 'edit' of Execute Sql Task
-Connection is established, package runs fine when my new variables are not in place.


Thank you.


  Post Attachments 
Variable1.jpg (3 views, 152.29 KB)
Variable 2.jpg (1 view, 76.56 KB)
Post #1477680
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse