September 5, 2011 at 2:01 pm
Hello,
I need to develop an SSIS package that does the following steps.
As part of the product feed for the Marketing Database we need to automate the following using SSIS:
1. Import the following files into REPLON1.dbo.crm_staging – appending _<<market code>> to the end to differentiate between markets. The market code should be from the REPLON1.crm_build.dbo.market table, matching on marketed:
o UK: \\viperlon1\_development\_live\import\UK\WEBITEM5Daily\
o US: \\viperlon1\_development\_live\import\US\WEBITEM5Daily\
o DE: \\viperlon1\_development\_live\import\DE\WEBITEM5Daily\
o AT: \\viperlon1\_development\_live\import\AT\WEBITEM5Daily\
o FR: \\viperlon1\_development\_live\import\FR\WEBITEM5Daily\
NB: please truncate tables, as opposed to dropping & re-creating each time
2. Use the data from above to populate the table REPLON1.dbo.crm_build.product_stockLevel using a stored procedure called udpproduct_stockLevel_insert_bulk (see below for an example stored procedure with error handling):
o productID is the same as ItemID in REPLON1.Product.dbo.ItemMPLANReference, where EDP in this table maps to the ItemNo field in the WEBITEM5 file
o marketID is from REPLON1.crm_build.dbo.market
o stockLevelTypeID refers to REPLON1.crm_build.dbo.stockLevelType
o stockLevel is the number of units on order/on hand
o stockLevelDate is the date the order is due or the units are on hand
o statusID & statusTypeID can be ignored for the time being
I don't know how to approach this. Could you help.
Debra
September 6, 2011 at 7:27 am
Hi there,
For your first step, you have a few options you can use.
The "easiest" option, IE the one that will require the least time up-front, would be to just make five separate data flow tasks, and in each one, have a separate flat file connection manager pointing to the specific file you want to import.
I don't know what your instructions mean regarding "append _<<market code>> to the end", but I doubt that would be too difficult to do, just append it whereever necessary in your data flow task.
The more scalable option though, would be to instead use a FOREACH Loop Container to loop through your folders. You'll need to use the Foreach from Variable enumerator, and you'll need to use a Script Task beforehand to build up a variable containing all the files you want to use. This can be done using the System.IO File and Directory classes. Once you build the object containing your file names, you can use the Foreach from Variable to loop through them.
For your second part, I can only assume that the parameters to pass in to the stored procedure all come from the data which was inserted in step 1.
If that's the case, then your best bet is to again use a FOREACH loop container. This time, you use the FOREACH from ADO Enumerator. You'll need to create an ADO object source variable which will query your data table that you built up from step 1, and then create a set of expressions to store the different variables you need for your stored procedure. Then you use the Execute SQL task, with the stored procedure, and pass it the different parameters that you need, using the expressions gathered earlier.
September 8, 2011 at 3:44 am
Thanks.
I agree that foreach loop container is the best option to loop through the folders. What I am unsure of is the script component to identify which folder path data goes into which table. How do i write this script? any thoughts?
once I am done with the first part i will have more confidence to go into the second part. Thanks for these guidelines, they are really helpful
September 8, 2011 at 9:29 am
See attached .dtsx, should be able to handle what you're looking for. Just add it to a project and take a look at the code. You'll need to modify the directory that I've used in the first script task, you should put "\\viperlon1\_development\_live\import\" as your directory.
September 8, 2011 at 1:58 pm
hi
thanks for sending this. I dont think i can change anything..it just opens as an executable file. and when i try to execute it asks for path..can i see it as a package in SSIS designer view
September 8, 2011 at 2:18 pm
Yeah what you want to do is, in your solution explorer, right click, and choose to add existing package, then select the .dtsx file I included.
September 8, 2011 at 2:26 pm
thanks. I can see the logic now. You have declared Files as object variable. could you explain me the script below:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports System.Collections
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dim di As New DirectoryInfo("C:\AppsBur")
Dim directories() As DirectoryInfo = di.GetDirectories
Dim files As ArrayList = New ArrayList
For Each directory As DirectoryInfo In directories
Dim fileArray() As FileInfo = directory.GetFiles()
For Each file As FileInfo In fileArray
files.Add(file.FullName)
Next
Next
Dts.Variables("Files").Value = files
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
September 8, 2011 at 2:36 pm
Like you mentioned, "Files" is a variable of type Object.
What is happening in the script is, I'm taking a given directory, and looping through all of the subdirectories in that directory.
For each subdirectory, I'm looping through all the files, and adding the full path of the file to the Files object.
Then, in the next step, I'm iterating through the Files object, and in the sample Script task, just displaying a message box indicating the name of the file.
What you would do instead, is have a Data Flow task, with a Flat File Connector. The Connection String property of the Flat File Connector would be dynamic, and supplied by the variable with the name File.
Then you can continue and export the data into your table. If you wanted to append that market code, you would just need to create a new column with the derived column transformation, and manipulate the File variable to get the string you want.
September 8, 2011 at 2:46 pm
Like you mentioned, "Files" is a variable of type Object.
What is happening in the script is, I'm taking a given directory, and looping through all of the subdirectories in that directory.
For each subdirectory, I'm looping through all the files, and adding the full path of the file to the Files object.
---I think here we need to make a small tweak to the script component that it appends the market code by running a query in crm_build..market database. So i dont know how it is possible in a script component. These need to be stored in the Object and passed on in filename..we also need to use this later on to create specific tables.
September 8, 2011 at 2:50 pm
Why do you need to do that?
Are you trying to only get back the files which have that market code?
Or, are you trying to add that market code to the database later?
September 8, 2011 at 2:52 pm
no, the paths below:
o UK: \\viperlon1\_development\_live\import\UK\WEBITEM5Dailyo US: \\viperlon1\_development\_live\import\US\WEBITEM5Dailyo DE: \\viperlon1\_development\_live\import\DE\WEBITEM5Dailyo AT: \\viperlon1\_development\_live\import\AT\WEBITEM5Dailyo FR: \\viperlon1\_development\_live\import\FR\WEBITEM5Daily
are specific to each market. we need an object that has all markets together, and that needs to be inserted in filepath and later on to create specific tables
September 8, 2011 at 3:00 pm
If I understood correctly, all your markets are contained within the same directory:
"\\viperlon1\_development\_live\import\"
Then, from there, there is a set of subdirectories.
Actually, I realise now that the script isn't quite right - You actually want to go one level further, since you have a separate directory for each country, and then within those directories, a separate directory for your particular files.
So what you would need to do is modify the script to be something like:
For Each directory As DirectoryInfo In directories
Dim subdirectories() As DirectoryInfo = directory.GetDirectories()
For Each subdirectory As DirectoryInfo In subdirectories
If subdirectory.Name = "WEBITEM5Daily" Then
Dim fileArray() As FileInfo = subdirectory.GetFiles()
For Each file As FileInfo In fileArray
files.Add(file.FullName)
End If
Next
Next
Next
This will populate your object with the file contents for each subdirectory.
September 8, 2011 at 3:06 pm
yeah u r right in ammending the script. Yes, all my markets are in the same sub-directory but the data is specific to each market. so when i am creating market-specific tables in my database, the correct file has to go and land there which is specific to that market.
September 8, 2011 at 3:12 pm
You're saying that the data is not all going in to REPLON1.dbo.crm_staging?
So, for example, you'll have a table like REPLON1.dbo.crm_staging_UK, and another one like REPLON1.dbo.crm_staging_FR, etc...?
If that's the case, would it not be simpler to just have one table, REPLON1.dbo.crm_staging , with a column inside that indicates the particular country?
September 8, 2011 at 3:15 pm
You're saying that the data is not all going in to REPLON1.dbo.crm_staging?
So, for example, you'll have a table like REPLON1.dbo.crm_staging_UK, and another one like REPLON1.dbo.crm_staging_FR, etc...?
If that's the case, would it not be simpler to just have one table, REPLON1.dbo.crm_staging , with a column inside that indicates the particular country?
---yes i wil have a table in REPLON1.dbo.crm_staging_UK, REPLON1.dbo.crm_staging_FR, etc for each market. I cant change the design requirement..what u saying is right but the design requirement is that we have specific market tables
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply