Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Name an ODBC destination file with a date


Name an ODBC destination file with a date

Author
Message
tiffanyb
tiffanyb
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8308 Visits: 19449
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Andrew G
Andrew G
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2003 Visits: 2227
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8308 Visits: 19449
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Andrew G
Andrew G
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2003 Visits: 2227
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
tiffanyb
tiffanyb
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
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