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

Name an ODBC destination file with a date Expand / Collapse
Author
Message
Posted Monday, June 24, 2013 11:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 11, 2013 6:18 AM
Points: 2, Visits: 10
Hi

I currently have text files sent monthly that need to be imported into SQL server. I'm new to SSIS and have fumbled my way through importing each one using a data flow task for each file (flat file source and OLE DB Destination). Within my OLE DB Destination data flow, I've set the name of the table to match my import file, however, I'm wondering if there is a way to add the date of the file to the end of it. For example, one text file is called Branch. Is it possible to name the SQL table 'Branch_20130630' or something of the like?

Also, when I set the table name within the OLE DB Destination, is there an option that overwrites the table should it be there already? (in the case of incomplete data that would need to be sent/reloaded)

Thanks so much!

Tiffany
Post #1466847
Posted Monday, June 24, 2013 11:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:42 AM
Points: 5,317, Visits: 12,357
tiffanyb (6/24/2013)
Hi

I currently have text files sent monthly that need to be imported into SQL server. I'm new to SSIS and have fumbled my way through importing each one using a data flow task for each file (flat file source and OLE DB Destination). Within my OLE DB Destination data flow, I've set the name of the table to match my import file, however, I'm wondering if there is a way to add the date of the file to the end of it. For example, one text file is called Branch. Is it possible to name the SQL table 'Branch_20130630' or something of the like?

Also, when I set the table name within the OLE DB Destination, is there an option that overwrites the table should it be there already? (in the case of incomplete data that would need to be sent/reloaded)

Thanks so much!

Tiffany


I'm not 100% sure I understand your questions, but here goes:

1) Is there is a way to add the date of the file to the end of it?
I presume that the file name changes every month and you don't want to keep editing the SSIS package to accommodate that?

2) Is there an option that overwrites the table should it be there already?
Are you creating a new table for every file that you import? This would usually be considered bad practice - better to put all of the data in the same table and include extra column(s) to help you identify the source of the data (eg, file name)

There is no such option, however, it's easy enough to achieve the same outcome. Before your dataflow task, add an ExecuteSQL task which does a truncate on the table.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1466981
Posted Tuesday, June 25, 2013 1:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:42 PM
Points: 1,310, Visits: 1,786
You can enter a dynamic destination using an expression https://www.simple-talk.com/sql/ssis/working-with-property-expressions-in-sql-server-integration-services/

Leave your existing settings as they are, the destination will be changed by the expression on execution.
You'll need a variable which is populated by reading the file date somehow, probably use powershell, then use the variable in the OLE DB expression
Post #1467005
Posted Tuesday, June 25, 2013 1:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:42 AM
Points: 5,317, Visits: 12,357
foxxo (6/25/2013)
You can enter a dynamic destination using an expression https://www.simple-talk.com/sql/ssis/working-with-property-expressions-in-sql-server-integration-services/

Leave your existing settings as they are, the destination will be changed by the expression on execution.
You'll need a variable which is populated by reading the file date somehow, probably use powershell, then use the variable in the OLE DB expression


The destination here is a SQL Server table, not a flat file.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1467006
Posted Tuesday, June 25, 2013 1:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:42 PM
Points: 1,310, Visits: 1,786
You can change data access mode for the oleDB destination object to "table name or view name variable" then assign a value to the variable via an expression or other ssis task
Post #1467015
Posted Tuesday, June 25, 2013 12:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 11, 2013 6:18 AM
Points: 2, Visits: 10
Hi Phil and foxxo,

thank you both for your replies. After I knew what to search for, I found a way to make 3 package variables;

FileDate = getdate() --For now, it is possible that not all clients send their data monthly... We might have to backdate some
LastMonth = (DT_Date)DATEADD("D",-(Day( @[User::FileDate])), @[User::FileDate])
LoanFileName = "Loans" + (DT_WSTR, 4) YEAR(@[User::LastMonth]) +
RIGHT("0"+(DT_WSTR, 2) MONTH(@[User::LastMonth]),2) +
RIGHT("0"+(DT_WSTR, 2) DAY(@[User::LastMonth]),2)

Now my issue is trying to reference these variables within the tasks. I tried using the OLE DB Destination and setting the data access mode to Table name or view name variable. I can select my variable from the list, however I get an error message;

Opening a rowset for "Loans20130531" failed. Check that the object exists in the database.

So, I thought I would try and use an execute SQL task to create the table before the data flow task runs. I ran into some trouble when trying to name the create table command;

CREATE TABLE @[User::LoanFileName](
[Customer ID] varchar(50),
[Account Number] varchar(50),
[Major] varchar(50),
[Minor] varchar(50),
[Benefit] varchar(50),
[Status] varchar(50),
[OpenDate] varchar(50),
[ClosedDate] varchar(50),
[Original Loan Amount] varchar(50),
[Balance] varchar(50),
[Maturity Date] varchar(50)
)

The above doesn't work, but I read that you can replace the @[User::LoanFileName] with a ? and then map it in the parameter mapping section of the execute SQL task editor. I tried that, but its still not working. I think I'm stuck now.... Any ideas?
Post #1467311
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse