Dominic34
asked on
Create dynamic filename in SSIS
Hi,
I'm migrating a DTS package to SSIS. In the DTS, I have an ActiveX Script that set a variable (filename with date and time as the file extension) and a Dynamic Properties Task that set the filename to a Text-File destination feeded by a Transform Data Task that uses a SQL query that extract and format the data to the required field-length. That works fine.
Now, I need to do the same in SSIS. Flat-File Connection Manager ask for an existing file. But this file is created by the DTS, it doesn't exist yet. And anyway, filename should be dynamic.
I tried Raw File, where I can select a variable as filename, but the output is binary.
The result file is imported by a SAP ERP system, so it has to match the required fix field-length.
how can I do this in SSIS?
thanks for your time and help
I'm migrating a DTS package to SSIS. In the DTS, I have an ActiveX Script that set a variable (filename with date and time as the file extension) and a Dynamic Properties Task that set the filename to a Text-File destination feeded by a Transform Data Task that uses a SQL query that extract and format the data to the required field-length. That works fine.
Now, I need to do the same in SSIS. Flat-File Connection Manager ask for an existing file. But this file is created by the DTS, it doesn't exist yet. And anyway, filename should be dynamic.
I tried Raw File, where I can select a variable as filename, but the output is binary.
The result file is imported by a SAP ERP system, so it has to match the required fix field-length.
how can I do this in SSIS?
thanks for your time and help
You will be able to do the same thing with a csv destination. You will need to set the destination filename as either an expression or a variable. If variable you will be able to evaluate the variable as an expression.
ASKER
how can I set the destination as a variable? When setting up a flat-file connection, it ask for a valid file name, I have to input something there. How can I use Expression to dynamically change filename of a flat-file connection ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When i say "In the expressions section" you will have to click the ... button.
ASKER
ok good. I've set the property to my variable name. But don't know why, the variable never changes when I execute the ActiveX Script:
Function Main()
dim NomFic
NomFic = year(now()) & Pad2Left(month(now())) & Pad2Left(day(now())) & Pad2Left(hour(now())) & Pad2Left(minute(now()))
DTSGlobalVariables("APicki ng").value = DTSGlobalVariables("Path") .value + DTSGlobalVariables("APicki ngName").v alue + NomFic
Main = DTSTaskExecResult_Success
End Function
Function Pad2Left(ByVal Value)
Pad2Left = Right("0" & CStr(Value), 2)
End Function
I have set the ConnectionString Expression to the variable "APicking". The ActiveX is executed before the Data Flow Task. Variable is not ReadOnly. But it don't changes and I generate same filename everytime.
Function Main()
dim NomFic
NomFic = year(now()) & Pad2Left(month(now())) & Pad2Left(day(now())) & Pad2Left(hour(now())) & Pad2Left(minute(now()))
DTSGlobalVariables("APicki
Main = DTSTaskExecResult_Success
End Function
Function Pad2Left(ByVal Value)
Pad2Left = Right("0" & CStr(Value), 2)
End Function
I have set the ConnectionString Expression to the variable "APicking". The ActiveX is executed before the Data Flow Task. Variable is not ReadOnly. But it don't changes and I generate same filename everytime.
If you look at the properties of the variable and set EvaluateAsExpression you can actually set the value of the variable there. You shouldnt need to use a script task to do this.
ASKER
ok sorry, I had a bug in my variable. File create with correct name and data is fed into it. Still have a problem with the formatting. All rows are inserted into a single line in the file. There is no CR/LF, although Row Delimiter is CR/LF and preview of data is looking good...
ASKER
This is my extraction query:
SELECT DISTINCT
RTRIM( dbo.PAD(a.[vpmOrder], 22, ' ', 'L')) AS PO,
RTRIM(dbo.PAD(a.itmCode, 6, ' ', 'L')) AS ITEM,
'V' as TYPE,
RTRIM( dbo.PAD(a.[vpmNoColis], 20, ' ', 'L')) as COLIS,
dbo.PAD(a.[itmQtyReelle], 6, '0', 'L') AS QTY,
RTRIM( dbo.PAD(b.[orderDate], 8, ' ', 'L')) as DATE
FROM TB_vpmPreColisage a
INNER JOIN [TB_vpmOrder] b ON a.[vpmOrder] = b.[orderNO]
INNER JOIN [TB_vpmColis] ON TB_vpmColis.[vpmNoColis] = a.[vpmNoColis]
WHERE [orderCompleted] IS NOT NULL AND b.[orderTreated] IS NULL
In Flat-File Connection Manager, I selected an existing valid file and delimited the width of each columns. Row Delimited is CR/LF. Output file gives:
1007540 04235V S075400221200003520101115 1007540 09263V C075400041200001520101115 ....
all the extracted data into a single row....And the preview looks good.
What I'm doing wrong?
SELECT DISTINCT
RTRIM( dbo.PAD(a.[vpmOrder], 22, ' ', 'L')) AS PO,
RTRIM(dbo.PAD(a.itmCode, 6, ' ', 'L')) AS ITEM,
'V' as TYPE,
RTRIM( dbo.PAD(a.[vpmNoColis], 20, ' ', 'L')) as COLIS,
dbo.PAD(a.[itmQtyReelle], 6, '0', 'L') AS QTY,
RTRIM( dbo.PAD(b.[orderDate], 8, ' ', 'L')) as DATE
FROM TB_vpmPreColisage a
INNER JOIN [TB_vpmOrder] b ON a.[vpmOrder] = b.[orderNO]
INNER JOIN [TB_vpmColis] ON TB_vpmColis.[vpmNoColis] = a.[vpmNoColis]
WHERE [orderCompleted] IS NOT NULL AND b.[orderTreated] IS NULL
In Flat-File Connection Manager, I selected an existing valid file and delimited the width of each columns. Row Delimited is CR/LF. Output file gives:
1007540 04235V S075400221200003520101115 1007540 09263V C075400041200001520101115 ....
all the extracted data into a single row....And the preview looks good.
What I'm doing wrong?
ASKER
ok found it. I had to select "Ragged Right" in the Format properties of the Flat-File Connection Manager to have the CR/LF add at the end of each row.
works like a charm now
works like a charm now