July 25, 2016 at 6:41 pm
Hi all, hopefully I'm posting this in the correct spot. I have a need to create an SSIS package that will take a column of numbers and from a CSV or Excel file and run that through a task or something in sql to build an insert statement that will accept the variables. Where can I look up information like this or which transformation task would I look at.
Needing to do.
I have the CSV file with one column, then I have to look up the code for an event(class) get the key for that then add the taken date to it Then once all that is together put that into the Table grabbing multiple keys that exist in the database based on that event key.
I'm taking a manual import process that the data is transformed outside just in T-SQL and I want to automate it in SSIS.
Any advice would be greatly appreciated.
Hope this all makes since
July 26, 2016 at 12:36 am
Some parts aren't entirely clear, but the general idea doesn't seem too difficult.
If you could offer a concrete example, that would make it much easier to help you. (E.g. "I have one column with 3 rows that contain the values 3, 7, and 9. I want to create 3 different insert statements using each of these values that looks like this: INSERT INTO table (col1, col2) VALUES (3, getdate())...")
From what I can gather, it seems like a possible candidate for a ForEach Loop container on an object variable that has had your column of values fed into it. Then within that container, a simple Execute SQL task could take the value in each iteration and construct the insert statement either by accepting a parameter or building the statement out as an expression. You could achieve this in a data flow as well using a flat file source and the SQL command transform, but I have had performance issues with that approach that make me shy away from it now.
Again, a concrete example will better show your goal and intent.
July 26, 2016 at 2:55 am
Personally, this sounds like to me that you'll be using a Data Flow, with Merge Joins. Like Bantrim says though, without really knowing what your data looks like, I could be wrong. if you're able to supply some sample data 9it doesn't have to be real data, but it does need to to realistic), along with an example of what your end data looks like, we'll be able to point you in a much more definitive direction.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply