June 2, 2008 at 7:43 am
One part of a multipart SSIS job that I am trying to resolve is after moving data to a text file, to name that file with a time stamp (GETDATE()), and use a lookup table to attach a product name. The file path and name may look like this
‘2008-06-02 09:26:52.343_ProductGreen_Test1.txt’. If I can solve this, than I intend to use a loop container cycle through ten times, creating 10 files with different product names.
I had created a variable ‘FilePathName’, (e.g. ‘E:\Company\Team\’) as a string. I could not fine a way to add a timestamp to the variable, even using CAST or CONVERT. I tried using an expression in file connections expression property, with no success. I am sure this is relatively easy and solved many times by others, but I am stuck. Would you point me the steps to create a file name, date stamped and choosing a name from a user look up table? SSIS SQL2005 SP1. Thanks.
June 2, 2008 at 8:10 am
Hey David,
It is actually not that difficult.
a) Add a variable called varCurrentDateTime, with a variable type of datetime.
b) Add a SQL task pointing to any old OLEDB connection, with a SQL statement of 'Select getdate()'. Assign the value to the varCurrentDateTime variable
c) I am assuming you are going to loop over your products table or something. Please dont shoot me for the incorrect assumption, but for arguments sake, lets say you are... Add an object variable which will hold all your product names. Lets call it varProductName
d) Add a SQL task pointing to the OLEDB connection which does the selection of your products from your products table (select top 10 works for me...) . Assign the resultset to varProductName.
e) Add a For Each ADO enumerator which will loop on your object variable.
f) Add a variable called varFullDateTimeFileConnectionString with a type of string.
g) Add a scripting task inside the ADO enumerator. Make sure all the variables (foldername, currentdateandtime, productname) are set as readonly, with the varFullDateTimeFileConnectionString being set as readwrite.
h) Good luck on the .NET script. If you get stuck, please let me know. You will have to cast everything as strings, and at the very last step set your DTS variable equal to the "massaged" value
i) Add the dataflow. On the text file destination, on the expressions, make the connectionstring point to the variable created in point f. What this will do is to set your connectionstring of your file at runtime
Good luck
~PD
June 2, 2008 at 2:15 pm
Would this help:
DECLARE
@fileTimeStampVARCHAR(500),
@cmdTextVARCHAR(500),
@rootSourceFileVARCHAR(500),
@fullSourceFileVARCHAR(500),
@destinationFileVARCHAR(500)
--Start with file supplied by established operations
SET @rootSourceFile = 'C:\Files\SourceFileName'
SET @fullSourceFile = 'C:\Files\SourceFileName.txt'
--Get the time to stamp it with, then clean it up to YYYYMMDDHHMMSSMMM
SET @fileTimeStamp = CONVERT(VARCHAR, GETDATE(),121)
SET @fileTimeStamp = REPLACE(@fileTimeStamp, ':', '')
SET @fileTimeStamp = REPLACE(@fileTimeStamp, '-', '')
SET @fileTimeStamp = REPLACE(@fileTimeStamp, ' ', '')
SET @fileTimeStamp = REPLACE(@fileTimeStamp, '.', '')
--Compose the string for the filename with timestamp
SET @destinationFile = @rootSourceFile + '_' + @fileTimeStamp + '.txt'
--Build the cmd prompt copy command
SET @cmdText = 'COPY '
SET @cmdText = @cmdText + @fullSourceFile
SET @cmdText = @cmdText + ' '
SET @cmdText = @cmdText + @destinationFile
--Run the extended stored procedure
EXECUTE master.dbo.xp_cmdshell @cmdText
June 2, 2008 at 5:11 pm
Try this article..
June 4, 2008 at 1:10 pm
PD et.al.,
Your steps pointed me in the right direction, but I took the easy, convenient path to get up and running. I was able to create the project using the collection ‘Foreach Item Enumerator’ and hard coded 1 through 10 for the feed to my stored procedure parameter. This created my 10 text files datetime stamped. So far, so good.
Now that I could prove to myself that I could drive my project to create the files, I need to put the final changes on it. I created a lookup table with a field ‘SampleOrderID’ and ‘Name’ (of the product group). I need to loop through the lookup table capturing the ‘SampleOrderID’ numbers (1-10, instead of the hard coded numbers above). I made innumerable attempts to follow your steps (c through i) to create this, but I failed. I don’t have a good understanding of the ForEach ADO Enumerator.
I need to identify the lookup table, and capture and place values of the fields in variables.
In addition, my files need to have the format ‘yyyyddmm_productgroup_.txt’ by using a suggestion following your help,
@[User::FilePathAndName] +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) +
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
(DT_STR,4,1252) DatePart("hh",getdate()) +
(DT_STR,4,1252) DatePart("mi",getdate()) +
(DT_STR,4,1252) DatePart("ss",getdate()) +
(DT_STR,4,1252) DatePart("ms",getdate()) +
"_TestDate.doc"
I was able to place 10 discrete files in the directory (using datepart’ms’).
How might I assign/link the productgroup from my lookup table to this naming expression? I expect the answers are much about setting up the variables correctly, which often seemed confusing. Thank for the help from you and others.
Dave Hart
June 5, 2008 at 12:05 am
I have a for each ADO enumerator running without much hassles.
Lets say your variable name is varProductName
a) What I did is to have an execute SQL task which populates the variable. Once more, the variable has to be an OBJECT variable, because that is what the .ADO enumerator is going to expect
- Change your result set to "full result set"
- Lets say your sql statement is something like "select productname from yourtablewithproducts"
- In your resultset, change the resultseet to productname, and map User::varProductName
b) Add a precedence constraint of completion to your enumerator
c) In your For Each ADO enumerator, add the variable to the ADO object source variable.
TADA, your for each ado enumerator should work
June 6, 2008 at 7:46 am
TADA! Bravo! Bingo! Ok. My SSIS pkg works top to bottom. After banging away on the various properties and variables, I was able to make it all work. I am still digesting all the inner workings of the project.
One last, final step. On output, my txt files are configured as 20080606_test.txt. I need them to read 'date_ProductName.txt.'
When I created the Execute SQL Task to read and feed my LookUp table to the ADO Enumerator, I could not see how (and was not successful in my experiments) to capture and feed a second column [Name] to a string variable I could use in naming my file. My set up only captures the first column (SmplOrdID) and feeds that to the enumerator. I will provide screen shot in an attachment to make this clearer. (See doc Attachment)
There should be a relatively simple, direct means to apply the product name to the file name as each file is enumerated (capture the column smplordId, capture column name, feed the id to the stored procedure, feed the name to a variable, use variable to name file, begin again!) thanks again.
June 8, 2008 at 8:13 am
Happy that the suggestion could be implemented
ciao
PD
June 9, 2008 at 2:04 pm
Found and solved my problem. I hadn't recognized the significance of the 'index' column in the variable mapping. Once I had my second 'name' variable in position 2, and set it to index '1', it worked perfectly.
The entire scheme then mapped my lookup up table with the id enumerator in first position, the name enumerator in second position. So, as it enumerated the first id to my stored procedure, the name was enumerated through to my flat file name accurately.
Thanks for your patience while I worked my way through.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply