April 21, 2015 at 2:14 pm
Greetings to all experts in this forum, this is my first post.
Recently, I was given similar task except that we do have the field names and data types on a separate CSV file. That is the good news. The bad news is, there are hundreds of CSV files to be loaded into hundreds of tables.
Not so keen to create hundreds of SSIS packages by hand, I remember one blog here about generating SSIS packages dynamically using BIML. First, I create a Metadata table and populate it with Filename, ColName, DataType, etc. Then run a BIMLscript which reads the Metadata table and generate the SSIS packages in about 5 minutes. (Pause for applause :cool:)
I also have another BIMLscript that reads the Metadata table and generates the SQL (DDL) for creating all the tables. This certainly helpful first time. But later I realised it is even more useful because the Field specifications we got in that CSV file was either inaccurate or out of date. I just update the Metadata table. Then re-run the BIMLscript to generate both SSIS package and DDL only for the one needs to be fixed. The whole thing was done in two days.
Even more benefits when the client later told us that they need some kind of auditing (Row counts for Source, Loaded, Rejected). So now, all I need to do is change the BIMLscript and re-run to generate all the hundreds SSIS packages with auditing added.
Regards,
Albert B.
April 21, 2015 at 8:13 pm
axissolutionspl (4/21/2015)
Greetings to all experts in this forum, this is my first post.Recently, I was given similar task except that we do have the field names and data types on a separate CSV file. That is the good news. The bad news is, there are hundreds of CSV files to be loaded into hundreds of tables.
Not so keen to create hundreds of SSIS packages by hand, I remember one blog here about generating SSIS packages dynamically using BIML. First, I create a Metadata table and populate it with Filename, ColName, DataType, etc. Then run a BIMLscript which reads the Metadata table and generate the SSIS packages in about 5 minutes. (Pause for applause :cool:)
I also have another BIMLscript that reads the Metadata table and generates the SQL (DDL) for creating all the tables. This certainly helpful first time. But later I realised it is even more useful because the Field specifications we got in that CSV file was either inaccurate or out of date. I just update the Metadata table. Then re-run the BIMLscript to generate both SSIS package and DDL only for the one needs to be fixed. The whole thing was done in two days.
Even more benefits when the client later told us that they need some kind of auditing (Row counts for Source, Loaded, Rejected). So now, all I need to do is change the BIMLscript and re-run to generate all the hundreds SSIS packages with auditing added.
Regards,
Albert B.
Welcome aboard, Albert! And that's fantastic news! I don't know if you're so inclined but it would be outstanding if your wrote an article on how you made such quick work out of what many people would consider to be an overwhelming task.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2015 at 2:57 am
Actually, I followed the excellent blog by Marco Schreuder here: http://blog.in2bi.com/biml/creating-a-meta-data-driven-ssis-solution-with-biml-1-overview/
Yes there is a bit of learning, but if you just use his example as starting point is not that hard. And you will be rewarded.
Also note, depending which version of SQL Server you are using, might need to replace the OLEDB provider name on all BIML scripts. His example was for SQL 2008 R2 (SQLNCLI10.1) so I replaced it for SQL 2012 (SQLNCLI11).
Regards,
Albert B.
April 22, 2015 at 3:11 am
axissolutionspl (4/22/2015)
Actually, I followed the excellent blog by Marco Schreuder here: http://blog.in2bi.com/biml/creating-a-meta-data-driven-ssis-solution-with-biml-1-overview/Yes there is a bit of learning, but if you just use his example as starting point is not that hard. And you will be rewarded.
Also note, depending which version of SQL Server you are using, might need to replace the OLEDB provider name on all BIML scripts. His example was for SQL 2008 R2 (SQLNCLI10.1) so I replaced it for SQL 2012 (SQLNCLI11).
Regards,
Albert B.
Slightly off topic, but do you know whether SSIS packages can be generated from BIML in 2013 format yet? Last time I looked, 2012 format was the highest possible.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 22, 2015 at 4:07 am
Do you mean SQL 2014.? Yes, I think so. But I have not try myself. I am relatively newbie in BIML too.
April 22, 2015 at 4:34 am
axissolutionspl (4/22/2015)
Do you mean SQL 2014.? Yes, I think so. But I have not try myself. I am relatively newbie in BIML too.
Well, technically I mean SSDT 12.x.x.x, which is integrated with VS2013, which is where I do SSIS development for packages to run on SQL 2014.
Last time I tried generating packages from BIML in VS2013, it generated them in SSDT 11.x.x.x format. When you open one of these in VS2013, you get a message along the lines of 'the package has been upgraded'.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 22, 2015 at 5:06 am
Hello Lowell,
Thank you for sharing the SSIS package.
You've used only the script task for entire work, actually we're not going to implement the solution in that way.
As per our implementation,
We've a “For each loop” container on control flow of a package.
The first component of this container is Script task. It reads the CSV files from the specified folder and sets following 3 variables.
> SQLScriptForCreatingATable
> FileNamewithphyscialPath
> TableName
The second component is “Execute SQL task”, which will use the SQLScriptToCreateTable variable to create a table to SQL database.
The third component is “Data Flow task”.
Here the source component is using the FileNamewithphyscialPath variable to connect to source file and destination component will use “TableName” variable to connect to destination.
Now following are the problems for third component,
At design time, it does not allow to keep the value empty for source and destination variables, FileNamewithphyscialPath and TableName respectively.
We’ve to populate hard-coded values in variables for these components only then we’re able to do the mappings.
Also, it is not changing the source and destination to second file in loop as it has the metadata for the first file.
I think the metadata for source and destination over data flow task cannot be changed at runtime.
Can you please advise how to load source and destination components dynamically, runtime using the variables?
April 22, 2015 at 5:54 am
think the metadata for source and destination over data flow task cannot be changed at runtime.
This is correct. It needs to exist at design time.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 22, 2015 at 8:47 am
axissolutionspl (4/22/2015)
Actually, I followed the excellent blog by Marco Schreuder here: http://blog.in2bi.com/biml/creating-a-meta-data-driven-ssis-solution-with-biml-1-overview/Yes there is a bit of learning, but if you just use his example as starting point is not that hard. And you will be rewarded.
Also note, depending which version of SQL Server you are using, might need to replace the OLEDB provider name on all BIML scripts. His example was for SQL 2008 R2 (SQLNCLI10.1) so I replaced it for SQL 2012 (SQLNCLI11).
Regards,
Albert B.
Excellent. Thanks for the link, Albert.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply